sql - Doctrine - Symfony 3 querybuilder COUNT() 是错误的

Question

我的数据库中有这些数据:

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

在这种情况下，我希望:

    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;