Doctrine 1
  1. Doctrine 1
  2. DC-371

Lazy loading - doctrine makes extra queries into db

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Critical Critical
    • Resolution: Unresolved
    • Affects Version/s: 1.2.0-BETA3
    • Fix Version/s: None
    • 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?

        Activity

        Hide
        Gennady Feldman added a comment -

        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.

        Show
        Gennady Feldman added a comment - 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.
        Hide
        Jonathan H. Wage added a comment -

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

        Show
        Jonathan H. Wage added a comment - Has anyone been able to reproduce this in a test case? I am not having much luck so far.
        Hide
        Luke Winiarski added a comment -

        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

        Show
        Luke Winiarski added a comment - 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
        Hide
        Jonathan H. Wage added a comment -

        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.

        Show
        Jonathan H. Wage added a comment - 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.
        Hide
        Roman Drapeko added a comment -

        Any comments? Will it be fixed??

        Show
        Roman Drapeko added a comment - Any comments? Will it be fixed??

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Roman Drapeko
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: