mysql - 将 SQL 查询转换为 Doctrine 1.2 dql

Question

我在 Mysql 表中有一个选择查询，用于获取特定列(日期)中具有重复值的相关表。如果列中有重复值，这将成功显示列及其外键。例如，两行具有相同的值日期列中的 (2014-11-10)

mysql>select man_id,date_created,count(date_created) as count
from collections
group by man_id,date_created
having count(date_created) > 1;

我希望这个查询转换为 Doctrine 查询，因为我使用 symfony 1.4 作为框架

 public function getDuplicateDatePayment() {
    $q = $this->createQuery()
              ->select('man_id','date_created','count(date_created) as count')
              ->from('Collections')
              ->groupBy('man_id','date_created')
              ->having('COUNT(c.date_created) > 1');
        return $q->execute();
}

SELECT c.id AS c__id, c.man_id AS c__man_id FROM collections c GROUP BY c.man_id HAVING count(c.date_created) > 1 //result 1 row

为什么学说查询没有按预期显示结果？如何转换该学说查询，使其显示类似于 SQL 的结果？

//结果1141行

更新

Collections 表与 Man 表存在一对多关系。我是否必须使用 innerJoin 来实现此目的？

Answer 1

经过两天的尝试和寻找答案，我终于找到了解决问题的方法。

SELECT c.id AS c__id, c.man_id AS c__man_id FROM collections c GROUP BY c.man_id HAVING count(c.date_created) > 1 

上述sql结果的问题是，它在groupBy子句中不包含'c.loan_id'，即使我将其包含在groupBy子句中。解决方案是，只需添加一个单独的addGroupBy子句..现在它工作.干杯..

public function getDuplicateDatePayment() {
$q = $this->createQuery()
          ->select('c.man_id','c.date_created','count(c.date_created) as count')
          ->from('Collections')
          ->groupBy('c.man_id')
          ->addGroupBy('c.date_created')
          ->having('count(c.date_created) > 1');
    return $q->execute();

}