Doctrine - Symfony 3 querybuilder COUNT() is wrong

Question

I've got this data in my DB:

I try to get the foreign_ids with the latest created_at value of a guest_identifier.

In this case I would expect:

    foreign_id: 5 for guest_identifier: 12345
    foreign_id: 5 for guest_identifier: 2345
    foreign_id: 4 for guest_identifier: 345

Now I want to count this results and return something like:

[
   {
       "foreign_id": 5,
       "occurrence": 2
   },
   {
       "foreign_id": 4,
       "occurrence": 1
   }
]

This is how I try to get this result:

$qb = $this->createQueryBuilder('statistic')
        ->select('statistic.foreignId, COUNT(statistic.foreignId) as occurrence')
        ->where('statistic.guideId = :guideId')
        ->andWhere('statistic.type = :type')
        ->andWhere('statistic.createdAt BETWEEN :startDate AND :endDate')
        ->groupBy('statistic.guestIdentifier')
        ->setParameters(array(
            'guideId' => $guideId,
            'type' => 'answer_clicked',
            'startDate' => $startDate,
            'endDate' => $endDate
        ))
        ->getQuery();

    $stats = $qb->getResult();

    return $stats;

The problem is, that my result looks like this:

[
  {
    "foreignId": 5,
    "occurrence": "3"
  },
  {
    "foreignId": 5,
    "occurrence": "3"
  },
  {
    "foreignId": 4,
    "occurrence": "2"
  }
]

I can't find, why the occurrence is 3 instead of 2 for foreign_id: 5 and why occurrence is 2 instead of 1 for foreign_id: 3. Also I don' know how to group the results another time.

score 1 · Accepted Answer · edited May 23 '17 at 12:31

I could solve my problem with this answer: https://stackoverflow.com/a/28090544/7069057

My function now looks like this:

$qb = $this->createQueryBuilder('statistic')
        ->select('statistic.foreignId, COUNT(statistic.foreignId)')
        ->where('statistic.guideId = :guideId')
        ->andWhere('statistic.type = :type')
        ->andWhere('statistic.createdAt BETWEEN :startDate AND :endDate')
        ->leftJoin('AppBundle\Entity\Statistic\Statistic', 'stat', Join::WITH,
            'statistic.type = stat.type AND statistic.guestIdentifier = stat.guestIdentifier AND stat.createdAt > statistic.createdAt')
        ->andWhere('stat.createdAt IS NULL')
        ->groupBy('statistic.foreignId')
        ->setParameters(array(
            'guideId' => $guideId,
            'type' => 'answer_clicked',
            'startDate' => $startDate,
            'endDate' => $endDate
        ))
        ->getQuery();

    $stats = $qb->getResult();

    return $stats;

Doctrine - Symfony 3 querybuilder COUNT() is wrong

1 Answers1