[DDC-1149] Optimize OneToMany and ManyToMany without join Created: 12/May/11  Updated: 30/Mar/13

Status: In Progress
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: Git Master
Fix Version/s: None
Security Level: All

Type: New Feature Priority: Major
Reporter: Andrey Kolyshkin Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 5
Labels: None

Attachments: File testDoctrine.php    

 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)


 Comments   
Comment by Benjamin Eberlei [ 12/May/11 ]

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

Comment by Andrey Kolyshkin [ 12/May/11 ]

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
}
Comment by Guilherme Blanco [ 10/Oct/11 ]

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. =)

Comment by Vladimir [ 25/Mar/13 ]

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

Generated at Wed Jun 19 00:53:25 UTC 2013 using JIRA 5.2.7#850-sha1:b2af0c8dc8537b36121c6a579fabbdf79fc919e5.