34074

Symfony : Pagination with inner join

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.

Answer1:

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 <strong>two FROM components</strong>, 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();

Answer2:

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();

Recommend

  • MySQL Nested Queries with Joins
  • SQL Query DATEDIFF date time fields result in minutes
  • doctrine how to write WhereIn() with another sql query inside
  • Reading an entire directory of .pdb files using BioPython
  • Nhibernate CreateSQLQuery Stored Procedure result to non mapped class
  • Sonata Admin configureListFields
  • sending data through submit using colorbox ajax
  • Convert a flat datastructure into a tree
  • Display Metadata-thumbnail of Jpeg in picturebox
  • Multiple update queries in doctrine and symfony2
  • Why does Doctrine QueryBuilder destroy my query?
  • Null argument error on MSDeploy
  • running Xvfb in background on remote linux
  • Appending to existing SQLite table when addition has fewer columns, without reading database into R
  • Modifying the AJAX PHP database example
  • how to check if a field is not unique
  • Reading Excel files in a locale independent way
  • nodejs tls session id
  • Can't connect using mysql_connect to Database in ipage.com hosting?
  • PHP MySQL generating unique random number
  • Parse returned C# list in AJAX success function
  • Hibernate manyToOne filter on base entity
  • Creating inner query in hibernate
  • ssh2_scp_send() using php corrupts pdf
  • Send money from Braintree
  • jQuery, Calling multiple animations in a row on click
  • PHP PDF generation problem
  • Align Excel cell to center VB - xlCenter is not declared
  • Could not install package 'Microsoft.Owin.Security 2.0.1'
  • How to force refresh on CallLog.Calls.CACHED_NAME column?
  • Submission of new app with iAds
  • Imports in __init__.py and `import as` statement
  • Escaping single quotes in JDBC with MySql
  • How to sort a same column both in asc order and desc order
  • Warning: strpos() expects parameter 1 to be string, resource
  • testing a POST using phpunit in laravel 4
  • Adding Parent and Child Nodes in TreeView from Sql Server 2008
  • SQL - Select lowest values with group by and order by?
  • Getting last autonumber in access
  • what is the difference between the asp.net mvc application and asp.net web application