Search code examples
symfonydqlhintpagerfanta

Symfony2 pagerfanta DQL with leftjoin

I'm trying to paginate a query that uses DQL (because I'm going to use some group functions like sum) and has an outer join, here a simplified code:

//in controller
use
Pagerfanta\Pagerfanta,
Pagerfanta\Adapter\DoctrineORMAdapter as PagerAdapter;

//in list action
$query=$em->createQuery(
   'select m,sum(d.amount) from 
   ABundle:Master m
   left join ABundle:Detail d with d.master=m
   group by m.date'
);

$query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, true);
$paginator=new Pagerfanta(new PagerAdapter($query));
$paginator->setMaxPerPage($this->getPerPage());
$paginator->setCurrentPage($this->getPage(),false,true);

return $paginator;

the problem is I get the following error:

An exception has been thrown during the rendering of a template ("Cannot count query which selects two FROM components, cannot make distinction")

I've been trying to set a hint called HINT_CUSTOM_OUTPUT_WALKER so that pagerfanta uses my query as a view and runs its own count but I haven't been able to solve this.

Can anyone please give me some guidance about how to properly use it?

Solution

  • Used the pagerfanta array adapter instead, it runs the query with the left join and then paginates the array with the result.