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..

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