理论- Symfony 3 querybuilder计数()是错误的

Question

我的数据库中有这样的数据：

​

​

我尝试使用foreign_ids的最新created_at值获取guest_identifier。

在这种情况下，我希望：

    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。另外，我也不知道如何下次对结果进行分组。

Answer 1

我可以用以下答案来解决我的问题：https://stackoverflow.com/a/28090544/7069057

我的功能现在看起来如下：

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