Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1149

Optimize OneToMany and ManyToMany without join

    Details

    • Type: New Feature New Feature
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: Git Master
    • Fix Version/s: 2.5
    • Component/s: ORM
    • Security Level: All
    • Labels:
      None

      Description

       
      /**
       * @Entity
       * @Table(name="users")
       */
      class User {
      
          /**
           * @Column
           * @Id
           */
          public $user_id;
      
          /**
           * @Column
           */
          public $email;
      
          /**
           * @OneToMany(targetEntity="Language", mappedBy="user",fetch="EAGER")
           */
          public $languages;
      
      }
      
      /**
       * @Entity
       * @Table(name="user_languages")
       */
      class Language {
      
          /**
           * @Column
           * @Id
           */
          public $user_language_id;
      
          /**
           * @ManyToOne(targetEntity="User", inversedBy="languages")
           * @JoinColumn(name="user_id", referencedColumnName="user_id")
           */
          public $user;
      
          /**
           * @Column
           */
          public $user_id;
      }
      
      $users = $em->getRepository('User')->findAll();
      

      Result:

      SELECT t0.user_id AS user_id1, t0.email AS email2 FROM users t0
      SELECT t0.user_language_id AS user_language_id1, t0.user_id AS user_id2, t0.user_id AS user_id3 FROM user_languages t0 WHERE t0.user_id = ?
      array(1) {
        [0]=>
        string(1) "1"
      }
      array(1) {
        [0]=>
        NULL
      }
      SELECT t0.user_language_id AS user_language_id1, t0.user_id AS user_id2, t0.user_id AS user_id3 FROM user_languages t0 WHERE t0.user_id = ?
      array(1) {
        [0]=>
        string(1) "2"
      }
      array(1) {
        [0]=>
        NULL
      }
      SELECT t0.user_language_id AS user_language_id1, t0.user_id AS user_id2, t0.user_id AS user_id3 FROM user_languages t0 WHERE t0.user_id = ?
      array(1) {
        [0]=>
        string(1) "3"
      }
      array(1) {
        [0]=>
        NULL
      }
      
      ...
      

      Need result:

      SELECT t0.user_id AS user_id1, t0.email AS email2 FROM users t0
      SELECT u0_.user_language_id AS user_language_id0, u0_.user_id AS user_id1, u0_.user_id AS user_id2 FROM user_languages u0_ WHERE u0_.user_id IN (1, 2, 3)
      

        Activity

        Hide
        Benjamin Eberlei added a comment -

        Sure you are on git master? this should be optimized already with fetch=EAGER

        Show
        Benjamin Eberlei added a comment - Sure you are on git master? this should be optimized already with fetch=EAGER
        Hide
        Andrey Kolyshkin added a comment -

        Attach test file

        I run

        git clone git://github.com/doctrine/doctrine2.git
        git clone git://github.com/doctrine/common.git
        git clone git://github.com/doctrine/dbal.git
        

        and run testDoctrine.php

        Result

        
        SELECT t0.user_id AS user_id1 FROM users t0
        
        SELECT t0.post_id AS post_id1, t0.user_id AS user_id2 FROM posts t0 WHERE t0.user_id = ?
        
        array(1) {
          [0]=>
          string(1) "1"
        }
        array(1) {
          [0]=>
          NULL
        }
        SELECT t0.post_id AS post_id1, t0.user_id AS user_id2 FROM posts t0 WHERE t0.user_id = ?
        
        array(1) {
          [0]=>
          string(1) "2"
        }
        array(1) {
          [0]=>
          NULL
        }
        SELECT t0.post_id AS post_id1, t0.user_id AS user_id2 FROM posts t0 WHERE t0.user_id = ?
        
        array(1) {
          [0]=>
          string(1) "3"
        }
        array(1) {
          [0]=>
          NULL
        }
        
        Show
        Andrey Kolyshkin added a comment - Attach test file I run git clone git: //github.com/doctrine/doctrine2.git git clone git: //github.com/doctrine/common.git git clone git: //github.com/doctrine/dbal.git and run testDoctrine.php Result SELECT t0.user_id AS user_id1 FROM users t0 SELECT t0.post_id AS post_id1, t0.user_id AS user_id2 FROM posts t0 WHERE t0.user_id = ? array(1) { [0]=> string(1) "1" } array(1) { [0]=> NULL } SELECT t0.post_id AS post_id1, t0.user_id AS user_id2 FROM posts t0 WHERE t0.user_id = ? array(1) { [0]=> string(1) "2" } array(1) { [0]=> NULL } SELECT t0.post_id AS post_id1, t0.user_id AS user_id2 FROM posts t0 WHERE t0.user_id = ? array(1) { [0]=> string(1) "3" } array(1) { [0]=> NULL }
        Hide
        Guilherme Blanco added a comment -

        Please instead of using fetch="EAGER", please use fetch="EXTRA_LAZY". It would fix your issue.
        I have successfully tested this situation in 2.2-DEV and it works like a charm. =)

        Show
        Guilherme Blanco added a comment - Please instead of using fetch="EAGER", please use fetch="EXTRA_LAZY". It would fix your issue. I have successfully tested this situation in 2.2-DEV and it works like a charm. =)
        Hide
        Vladimir added a comment - - edited

        Doctrine ORM 2.3.3 (Symfony2.2) - using LAZY or EXTRA_LAZY fetch mode there are only one query for:

        $users = $em->getRepository('User')->findAll();

        but additional users_count queries for

        foreach($users as $user) $user->languages->toArray()

        And if use fetch EAGER - for some reason there are 2 x users_count queries , ie each query

        SELECT t0.post_id AS post_id1, t0.user_id AS user_id2 FROM posts t0 WHERE t0.user_id = ?

        with unique user_id executed twice

        Show
        Vladimir added a comment - - edited Doctrine ORM 2.3.3 (Symfony2.2) - using LAZY or EXTRA_LAZY fetch mode there are only one query for: $users = $em->getRepository('User')->findAll(); but additional users_count queries for foreach($users as $user) $user->languages->toArray() And if use fetch EAGER - for some reason there are 2 x users_count queries , ie each query SELECT t0.post_id AS post_id1, t0.user_id AS user_id2 FROM posts t0 WHERE t0.user_id = ? with unique user_id executed twice
        Hide
        Konstantin added a comment -

        Please fix this issue

        Show
        Konstantin added a comment - Please fix this issue
        Hide
        Madhav Krishna added a comment - - edited

        Is this likely to be resolved soon? Or is there a good workaround that we could implement?

        Show
        Madhav Krishna added a comment - - edited Is this likely to be resolved soon? Or is there a good workaround that we could implement?
        Hide
        CoL added a comment -

        Any news on this issue?

        Show
        CoL added a comment - Any news on this issue?
        Hide
        Flip added a comment -

        sounds very useful !

        Show
        Flip added a comment - sounds very useful !
        Show
        Guilherme Blanco added a comment - As of https://github.com/doctrine/doctrine2/commit/b28fa9a05a868d42c9b161cda3c73a8c5822acb4 this issue is fixed

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Andrey Kolyshkin
          • Votes:
            15 Vote for this issue
            Watchers:
            18 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: