Doctrine Query Builder Where Count of ManyToMany is greater than

Question

Im using the Doctrine Query Builder, and have a very specific requirement that came through.

I am using the ManyToMany field in my entity, related to User entity association (Array of User account entities).

/**
 * @var ArrayCollection
 *
 * @ORM\ManyToMany(targetEntity="User", cascade={"persist"})
 * @ORM\JoinTable(name="post_user_list")
 */
protected $userList;

Amongst the requirements of displaying "public posts" requires that the Entity have a published boolean set to true, a published date less than the current date, and two users associated with entity.

In my query builder, I have setup this:

$qb = $this->getEntityManager()->createQueryBuilder();
$qb->select($select)->from($this->getEntityName(), 'p');
$criteria = $qb->expr()->andX();
$criteria->add($qb->expr()->eq('p.editor_published', 1))
         ->add($qb->expr()->lte('p.datePublished', ':now'));

and that only handles the first two requirements, now I need a criteria entry for counting the amount of user entities in userList, and the where clause specifically for greater than or equal to two users.

Not exactly sure where to proceed..

Goulet

Answer 1

Try this. The query uses HAVING to only display entities that are associated with 2 or more users.

$qb->select($select)
    ->from($this->getEntityName(), 'p')
    ->innerJoin('p.userList','u')
    ->where('p.editor_published = 1')
    ->andWhere('p.datePublished <= :now')
    ->groupBy($select) //not sure what's in $select may need to change this
    ->having('count(u.id) > 1'); //assuming user has an id column otherwise change it
    ->setParameter('now',new \DateTime());

Ecumenicalism

Hi! Thanks for the answer! Ill give it a shot when i get into work. This is going to sound incredible asinine of me, but I specifically used the ManyToMany doctrine relationship to prevent having to write any kind of Joins and whatnot. Is this unfortunately my only path? Also I am using the andX() stuff for the reason that this particular repository method has a lot of complex context heavy clauses that builds onto this, and it got cumbersome managing what and where was an andWhere or orWhere and such. I just figured that the Doctrine Criteria clauses was powerful enough to be able to do this – Goulet

If you want to avoid joins you can get the count by subquery i.e. '(SELECT COUNT(u.id) FROM MyNameSpace\User u WHERE u MEMBER OF p.ul) > 1' – Ecumenicalism

ok so i am indeed bound to do it the somewhat 'classic' mysql way of handling the problem. That kind of sucks to be honest haha so much automatic magic going on it's hard not to hope for a more managed solution. Ill give em a shot, thanks! – Goulet

hoping for something along the lines of: $criteria->add($qb->expr()->gte($qb->expr()->count('p.userList'), 2)) – Goulet

Your original solution in the answer worked out pretty good actually. It's still disappointing it had to be that way for some reason, but the effect is perfect. Thank you so much :) – Goulet

Glad it worked. A few weeks ago I was also looking for similar syntax to count('p.userList') > x. Seems intuitive but I wasn't able to find it and ended up with joins or subqueries. – Ecumenicalism