Symfony : Pagination with inner join

Question

I need to implement a pagination. Seemingly, Doctrine doesn't support some jointures.

Here is my query :

$query = $this->getEntityManager()
              ->createQueryBuilder();

$query->setFirstResult(($page - 1) * $maxperpage);
$query->setMaxResults($maxperpage);

$query->select('d')
      ->from('DemandeBundle:Declaration', 'd')
      ->orderBy('d.id', 'ASC')
      ->innerJoin('ContactBundle:Contact', 'c', 'WITH', 'd.contact = c')
      ->where('c.structure_id = :structure_id')
      ->setParameter('structure_id', $structureId)
      ->getQuery()
      ->getResult();

return new Paginator($query, true);

It's working fine when I am not using innerJoin but I need to use it so as to display only requests regarding to my user.

Using innerJoin I got that kind of error :

"An exception has been thrown during the rendering of a template 
("Cannot count query which selects two FROM components, cannot make distinction") in   
DemandeBundle:Demande:listing_demande.html.twig at line 25"

How can I circumvent this problem without using another bundle or whatever.

Hope you will understand me guy.

Answer 1

Finally, I found out a solution :

Instead of :

$query->select('d')
      ->from('DemandeBundle:Declaration', 'd')
      ->orderBy('d.id', 'ASC')
      ->innerJoin('ContactBundle:Contact', 'c', 'WITH', 'd.contact = c')
      ->where('c.structure_id = :structure_id')
      ->setParameter('structure_id', $structureId)
      ->getQuery()
      ->getResult();

I used :

$query->select('d')
      ->add('from', 'SgaDemandeBundle:Declaration d INNER JOIN d.contact c')
      ->where('c.structure_id = :structure_id')
      ->setParameter('structure_id', $structureId)
      ->orderBy('d.id', 'ASC')
      ->getQuery();

Answer 2

Is your Declaration somehow related to Contact?

It's far better for you to have ManyToOne relation in Contact that points to Declaration. That way, it will work since you won't have two FROM components, but single one instead.

Then, modify the query to do:

->innerJoin('d.contant', 'c')

The full query should look like this:

$query->select('d')
  ->from('DemandeBundle:Declaration', 'd')
  ->orderBy('d.id', 'ASC')
  ->innerJoin('d.contact', 'c') // <-- THIS LINE IS CRITICAL
  ->where('c.structure_id = :structure_id')
  ->setParameter('structure_id', $structureId)
  ->getQuery()
  ->getResult();