Ok, so I understand the first point. This is good. Let's talk about the actual one ^^
In the same context, and with the same query. The final query (the first one) that is generated is : SELECT > a.* > FROM > ( > SELECT > DISTINCT ID_VARIABLE2 > FROM > ( > SELECT > v0_.LIBELLE AS LIBELLE0, > v0_.MNEMONIQUE AS MNEMONIQUE1, > v0_.ID_VARIABLE AS ID_VARIABLE2, > p1_.ID_PROFIL AS ID_PROFIL3 > FROM > VARIABLE v0_ > LEFT JOIN variable_profil v2_ ON v0_.ID_VARIABLE = > v2_.ID_VARIABLE > LEFT JOIN PROFIL p1_ ON p1_.ID_PROFIL = v2_.ID_PROFIL > ORDER BY > v0_.LIBELLE ASC > ) dctrn_result > ) a > WHERE > ROWNUM <= 100 I'm working with Oracle, which is not using the LIMIT statement as MySQL (and others) do. It's replaced by the ROWNUM statement. The issue is that my query does not sort and paginate the expected part of the result. I'm debugging this situation since yesterday, and while I wase executing the query manually (in a SQL Developer client) I've found a clue of the problem : DISTINCT With the DISTINCT, the ORDER BY the results are not good. If I remove it, it appears to be good. Do you have any idea ? Le mercredi 11 juin 2014 16:17:50 UTC+2, Jàπ (Jasper N. Brouwer) a écrit : > > That part about the paginator is important information for us! > > When passing a query to the paginator, the paginator will (by default) > perform 2 queries: > - one to fetch _all_ ids of the root entities. > - one to fetch a single page (based on those root ids). > > This is needed to deal with queries containing JOIN clauses. > > When you join tables `a` and `b`, and limit on say 5 rows, those rows > could contain the following: > > a.id | b.id > -----+----- > 1 | 1 > 1 | 2 > 1 | 3 > 2 | 4 > 3 | 5 > > Doctrine will hydrate this into entities, which will result in an array > with only 3 _root_ entities. So you don't get an array with 5 entities you > would expect (when telling the paginator to limit on 5 entities). > > PS: You could turn this behaviour off by passing FALSE as second argument > to the paginator. But be ware that you should only do this when the query > _doesn't_ contain JOIN clauses! > > -- > Jasper N. Brouwer > (@jaspernbrouwer) > > > On 11 June 2014 at 16:03:50, [email protected] <javascript:> ( > [email protected] <javascript:>) wrote: > > What var_dump($query->getDQL()) output (before I give my query to a > > Doctrine paginator) : > > > > SELECT > > > partial VARIABLE_PROFIL.{idProfil}, > > > partial VARIABLE.{idVariable,mnemonique,libelle,varSaep} > > > FROM > > > Acme\AdminBundle\Entity\Variable VARIABLE > > > LEFT JOIN VARIABLE.idProfil VARIABLE_PROFIL > > > ORDER BY VARIABLE.libelle asc > > > -- You received this message because you are subscribed to the Google Groups "doctrine-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/doctrine-user. For more options, visit https://groups.google.com/d/optout.
