Doctrine Query Builder count ManyToMany

Question

I have an entity Offer. Offer has a ManyToMany Relationship to Files. Now I want to have all Offers, that have files -> Count(offer.files) > 0.

I tried it like this, but doesn't work:

$this->repository->createQueryBuilder('offer')
            ->addSelect('COUNT(offer.files) as files')
            ->having('files > 1')
            ->getQuery()
            ->getResult();

Lucretialucretius

Answer 1

You need to inner join with the association and group by the root entity offer:

->innerJoin('offer.files', 'files')

The INNER JOIN keyword selects records that have matching values in both tables.

Then you can:

->addSelect('COUNT(files) as total')
->having('total > 1')
->groupBy('offer')

If you do not need the total in the result, you can define it as HIDDEN, change the 1st line above as:

->addSelect('COUNT(files) as HIDDEN total')

Inner join in detail Doctrine query builder

Pembroke

Answer 2

Actually you do not need a join. Doctrine has built in SIZE DQL function for that.

SIZE(collection) - Return the number of elements in the specified collection

So you could use it like this:

$this->repository->createQueryBuilder('offer')
    ->addSelect('SIZE(offer.files) as files')
    ->having('files > 1')
    ->getQuery()
    ->getResult();

Sabo

just discovered that this leads to the result being a nested array with element 0 being the entity and element 1 the value of the additional select. it works just fine however to omit the addSelect and do having('SIZE(offer.files) > 1') directly – Boult