Doctrine 2 - GROUP BY two columns, gets incorrect values-CodePudding

In my databases I have table with tasks related with user table. I want to get list of task in specific status, grouped by user and status. It is my query:

$this->createQueryBuilder('t')
      ->select('t.assignee, COUNT(t.id) as count, t.state')
      ->join('t.assignee', 'user')
      ->andWhere('t.state IN (:states)')
      ->setParameters([
         'states' => array($states)
      ])
      ->addGroupBy('t.assignee')
      ->addGroupBy('t.state')
      ->getQuery()
      ->getResult()

Unfortunately, that query do not return proper records. The result is only one record per every user, although it should returns some records for one user, sorted by task types. May you help me correct my query?

CodePudding user response：

You must set the groupBy method first and then use addGroupBy method after.

Your code will look like this :

$this->createQueryBuilder('t')
      ->select('t.assignee, COUNT(t.id) as count, t.state')
      ->join('t.assignee', 'user')
      ->andWhere('t.state IN (:states)')
      ->setParameters([
         'states' => array($states)
      ])
      ->groupBy('t.assignee')
      ->addGroupBy('t.state')
      ->getQuery()
      ->getResult()

CodePudding user response：

Change the andWhere to where too:

$em = $this->getDoctrine()->getManager();
$query = $em->getRepository('App:Entity')->createQueryBuilder('t');
$query
      ->select('t.assignee, COUNT(t.id) as count, t.state')
      ->join('t.assignee', 'user')
      ->where($query->expr()->in('t.state', array($states)))
      ->groupBy('t.assignee')
      ->addGroupBy('t.state')
      ->getQuery()
      ->getResult();