Symfony 2: knppaginator does not allow left join

Question

I use knppaginator for getting paginated results. When I use a LEFT JOIN statement in my query, I get the following error:

Cannot count query which selects two FROM components, cannot make distinction

The code in my controller looks like this:

 public function paginationAction()
    {
        $em    = $this->get('doctrine.orm.entity_manager');

        $dql   = "SELECT p.name, c.name
                  FROM MyBundle:Products p
                  LEFT JOIN MyBundle:Categories c
                  WITH c.id = p.categoryId";

        $query = $em->createQuery($dql);

        $request = $this->getRequest();
        $paginator = $this->get('knp_paginator');
        $pagination = $paginator->paginate(
            $query,
            $request->query->getInt('page', 1),
            10
        );
        $viewData['pagination'] = $pagination;

        return $this->render('MyBundle:results.html.twig', $viewData);
    }

How can I make the LEFT JOIN statement work? When I leave out the LEFT JOIN, everything works fine.

Answer 1

This question is asked before. Check out this answer: Doctrine : Pagination with left Joins