[DC-371] Lazy loading - doctrine makes extra queries into db Created: 19/Dec/09  Updated: 23/Dec/10

Status: Open
Project: Doctrine 1
Component/s: Behaviors, Documentation, Query, Record
Affects Version/s: 1.2.0-BETA3
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Roman Drapeko Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Symfony 1.4, Doctrine Version: 1.2.0-BETA3



 Description   

Just downloaded symfony 1.4

First of all I have a query:

$q = \Doctrine_Query::create()
->select('u., ur.')
->from('UserDb u')
->leftJoin('u.RealUserDetailsDb ur')
->leftJoin('u.MockUserDetailsDb um')
->where('u.id = :user_id')
;
$user = $q->fetchOne(array(':user_id' => $uid));

After that I'm accessing the fields of this object:

$userArray = array(
'id' => $this->getUser()->getId(),
'real_user_details_id' => $this->getUser()->getRealUserDetailsId(),
'mock_user_details_id' => $this->getUser()->getMockUserDetailsId(),
'real_user_details' => array(),
'mock_user_details' => array()
);

This is the actual queries into DB:

NR1:
SELECT u.id AS u_id, u.user_real_id AS uuser_real_id, u.user_mock_id AS uuser_mock_id, u2.id AS u2id, u2.nickname AS u2nickname, u2.email AS u2_email FROM user u LEFT JOIN user_real u2 ON u.user_real_id = u2.id LEFT JOIN user_mock u3 ON u.user_mock_id = u3.id WHERE (u.id = :user_id)

NR2:
SELECT u.id AS u_id, u.user_real_id AS uuser_real_id, u.user_mock_id AS u_user_mock_id FROM user u WHERE (u.id = '1') LIMIT 1

As you can see there are TWO queries however there should be only one query. The problem is that u.user_real_id is NULL in database and when I do 'real_user_details_id' => $this->getUser()->getRealUserDetailsId() doctrine does not have enough intelligence to understand that these fields have been already requested in NR1. If I comment this field, everything works well.

SURPRISE!
And now a surprise... if I modify a little bit my first query: ">select('u.')" instead of , ">select('u., ur.*')" it WON'T make TWO queries. It will make ONLY ONE!

As you understand this a very critical bug and of course our system won't go to production with this bug.

P.S. Is it possible to turn off the lazy loading in doctrine?



 Comments   
Comment by Roman Drapeko [ 17/Jan/10 ]

Any comments? Will it be fixed??

Comment by Jonathan H. Wage [ 01/Mar/10 ]

Hi, I'd like to take a look but can you make a failing test case that I can run so that I can see if I can come up with a patch that fixes your case and doesn't break anything else.

Comment by Luke Winiarski [ 01/Jun/10 ]

Hi

I had similar problem but after several hours i did work it out

Try to make get method in your model for getting field which has NULL value in database

public function getUserRealId()

{ return $this->_get("user_real_id", false); }

by making second argument false u force doctrine not to lazy load value and extra sql query is not created

regards

Comment by Jonathan H. Wage [ 08/Jun/10 ]

Has anyone been able to reproduce this in a test case? I am not having much luck so far.

Comment by Gennady Feldman [ 23/Dec/10 ]

I've seen this a ton of times. Basically when it loads related records through the Hydrator using leftJoin() and gets NULLs back. BUT it doesn't save the fact that the related records are NULL. So when you actually do call to getRelated objects it sees that it doesn't have the value cached and runs the query again.

Let me know if I should show you the problem in the Doctrine code base.

Generated at Mon Sep 01 21:57:02 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.