I have a query that will display columns with duplicate or with more than 1 values.I can display it using sql
select date_created,loan_id,count(1) as cnt
from collections
group by date_created,loan_id
having count(1)>1;
I want that to convert to Doctrine 1 query,I tried
public function getDuplicateDatePayment() {
$q = $this->createQuery('c')
->select('c.date_created,c.loan_id,c.count(1) as cnt')
->groupBy('c.date_created','c.loan_id')
->having('c.count(1) > 1');
return $q->execute();
}
But it only return errors.Any Idea on how to correctly convert the said working sql into a doctrine 1 query?
SQLSTATE[42000]: Syntax error or access violation: 1630 FUNCTION c.count does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual. Failing Query: "SELECT c.id AS c__id, c.date_created AS c__date_created, c.loan_id AS c__loan_id, c.count(1) AS c__0, c.count(1) AS c__0 FROM collections c GROUP BY c.date_created HAVING c.count(1) > 1"
I hope the problem may be with count. Try the following
public function getDuplicateDatePayment() {
$q = $this->createQuery('c')
->select('c.date_created,c.loan_id,count(c.1) as cnt')
->groupBy('c.date_created','c.loan_id')
->having('c.count(1) > 1');
return $q->execute();
}