In the first query, DISTINCT is used to fetch unique ids. The ORDER BY is in there because it's in your query, but theoritically it isn't needed here. This query should fetch _all_ ids, so the order isn't relevant. It's strange though there's a ROWNUM in the first query, as far as I know it shouldn't be there.
The second query is the one that will actually retreive the results that get hydrated. There should be no DISTINCT in there, and the ROWNUM should be. PS: I've no experience with Oracle (or Doctrine in combination with Oracle), so maybe someone else can elaborate on the ROWNUM in the first query? -- Jasper N. Brouwer (@jaspernbrouwer) On 11 June 2014 at 17:13:53, [email protected] ([email protected]) wrote: > 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 ? -- 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.
