Doctrine - Symfony 3查询构建器COUNT()错误

Question

我在我的数据库中有这些数据：

我尝试获取客人标识符的最新 created_at 值的 foreign_ids。

在这种情况下，我期望：

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

现在我想对这些结果进行计数，并返回类似如下的内容：

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

这是我尝试获得此结果的方式：

$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;

问题是，我的结果看起来像这样：

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

我找不到原因，为什么foreign_id为5时出现次数为3而不是2，以及为什么foreign_id为3时出现次数为2而不是1。另外，我不知道如何再次分组结果。

- Oudstand

Answer 1

我可以用这个答案解决我的问题：https://dev59.com/imct5IYBdhLWcg3wa83p#28090544 我的函数现在看起来像这样：

$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;

- Oudstand