Search code examples
symfonydoctrine-ormdql

doctrine2 paginator error for group by


I use this DQL to get some data but i get an error when using doctrine2 paginator:

SELECT f FROM DankeForumBundle:Forumusermessage f
  WHERE f.recipientdelete=0
  GROUP BY f.forwarder
  HAVING f.recipient=:recipient

This is because doctrine executes this query to count pages:

SELECT count(DISTINCT f.id) FROM Forumusermessage AS f
  WHERE f.recipientdelete=0
  GROUP BY f.forwarder_id
  HAVING f.recipient_id=:recipient

Is there any way to paginate this without using native SQL queries?

EDIT:

This is an example PHP code:

$query = $this->getEntityManager()->createQuery('SELECT f FROM DankeForumBundle:Forumusermessage f
  WHERE f.recipientdelete=0
  GROUP BY f.forwarder
  HAVING f.recipient=:recipient');
$query->setParameter('recipient', $user);
$query->setFirstResult(0);
$query->setMaxResults(10);
$paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($query);
echo count($paginator); // wrong page count

Solution

  • I found an workaround using subqueries:

    SELECT f FROM DankeForumBundle:Forumusermessage f
    WHERE
      f.recipientdelete=0 AND
      f.recipient=:recipient
      AND f.id IN (
        SELECT fn FROM DankeForumBundle:Forumusermessage fn
        GROUP BY fn.forwarder
      )