CODEWITHSUNDEEP
mysqldoctrine-ormquery-builder
Zwen2012
Zwen2012

Reputation: 3498

Doctrine Query Builder count ManyToMany

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();

Upvotes: 7

Views: 3205

Answers (2)

Laurynas
Laurynas

Reputation: 796

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();

Upvotes: 12

Jannes Botis
Jannes Botis

Reputation: 11242

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

Upvotes: 4

Related Questions