[DDC-1526] Unecessary queries with LEFT JOIN Created: 09/Dec/11  Updated: 23/Oct/12  Resolved: 28/Jan/12

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: None
Affects Version/s: 2.1.4
Fix Version/s: 2.1.6, 2.2
Security Level: All

Type: Bug Priority: Critical
Reporter: Pascal Burkhard Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

PHP 5.3.6

Attachments: Text File trace.txt     Text File trace.txt    
Issue Links:
Duplicate
is duplicated by DDC-1573 Simple relation hydratation not worki... Resolved

 Description   

After upgrading to 2.1.4 (from 2.1.2), the following dql started creating way more queries than necessary :
SELECT m, p, s, c, o
FROM FERMainBundle:Menu m
LEFT JOIN m.page p
LEFT JOIN m.section s
LEFT JOIN m.children c
LEFT JOIN s.position o
ORDER BY m.lft

Details to the code here:
http://pastie.textmate.org/private/z9gtgqe1odwenxcmudywqa
The model looks like that:
http://pastie.textmate.org/private/przxzfimsfyua02cxqcv9a

http://pastie.textmate.org/private/ob1jqiekv89e4xj9bq06q
First query is executed, it should in fact retrieve everything there is about the menu, but then it runs the second query for every menu element I have, generating a lot of queries that didn't occur before.

 Comments   
Comment by Benjamin Eberlei [ 15/Dec/11 ]

Can you profile where exactly the extra queries are executed using xdebug_start_trace? Directly during hydration or later in your code?

Comment by Pascal Burkhard [ 19/Dec/11 ]

xdebug trace start just before I query the database

Comment by Benjamin Eberlei [ 20/Dec/11 ]

Hi Pascal, sorry but this is not enough. I need this query until all the other entities (or at least one) are n+1 joined.

Comment by Pascal Burkhard [ 20/Dec/11 ]

Here the complete trace, started just before the first query. I'm sorry but I can't make heads or tails with that... I hope it can help you pinpoint the problem.

Please also note that I have update Doctrine ORM to 2.1.5 and there was a change in the number of "superfluous" queries done. I am now only left with additional queries to get the relations to "parent", cf the model ( http://pastie.textmate.org/private/przxzfimsfyua02cxqcv9a ).

Comment by Benjamin Eberlei [ 28/Dec/11 ]

Is the trace from before upgrade to 2.1.5 or after?

Comment by Pascal Burkhard [ 28/Dec/11 ]

The "complete" trace, i.e. the one that is 5.94 mb big is from after the upgrade to 2.1.5.

Comment by Benjamin Eberlei [ 29/Dec/11 ]

Can you disable the nested set extension? the other ticket uses it too and i want to rule out that its the extensions fault.

Comment by Pascal Burkhard [ 29/Dec/11 ]

Alright. I deactivated the Tree extension, but there are no changes in the number of queries.

Comment by Benjamin Eberlei [ 28/Jan/12 ]

I found the issue.

Comment by Benjamin Eberlei [ 28/Jan/12 ]

Fixed

Generated at Sun May 19 00:16:15 UTC 2013 using JIRA 5.2.7#850-sha1:b2af0c8dc8537b36121c6a579fabbdf79fc919e5.