How to constrain a query for number of items in ObjectStorage property in Extbase?

Question

I want to build queries in my Extbase repository that can filter out objects with a certain number of items. items is of type ObjectStorage in the model.

I tried to get objects with at least 1 item in this query, but it obviously doesn't work because the method "greater than" won't count the items in the objectStorage.

$x = 0; //any number
$query = $this->createQuery();
$constraints[] = $query->equals('deleted', 0);
$constraints[] = $query->equals('hidden', 0);
$constraints[] = $query->greaterThan('items', $x);
return $query->matching($query->logicalAnd($constraints))->execute();

So how can I do this?
I was thinking about an SQL Statement, but how could I add it to constraints?
I don't want to do everything with SQL.

Answer 1

You can create yourself a custom constraint that does this. Have a look at how it is done in the \TYPO3\CMS\Extbase\Persistence\Generic\Qom\QueryObjectModelFactory.

Answer 2

I don't see how you could do it without either using $query->statement or iterating the whole result without the "count" constraint and then throwing away the records with items lower than $x. (Apart from pgampe's approach that was posted at the exact same time.)

Which approach you take depends on performance considerations. If you have a small result set, doing a foreach on the QueryResult wouldn't hurt so much. But since every result is converted into an object in the process, you'll have a big overhead by doing so when you have a lot of records.

Keep in mind that using $query->statement won't make you lose the ability to work with the records as domain objects. So it's not a big deal to go this way.