DDC-1149: Optimize OneToMany and ManyToMany without join #1749

Closed
doctrinebot opened this Issue May 12, 2011 · 11 comments

2 participants

@doctrinebot

Jira issue originally created by user morfi:

/****
 * @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)
@doctrinebot

Comment created by @beberlei:

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

@doctrinebot

Comment created by morfi:

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
}
@doctrinebot

Comment created by @guilhermeblanco:

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

@doctrinebot

Comment created by fludimir:

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

@doctrinebot

Comment created by koc:

Please fix this issue

@doctrinebot

Comment created by madhkrish:

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

@doctrinebot

Comment created by col:

Any news on this issue?

@doctrinebot

Comment created by flip101:

(y) sounds very useful !

@doctrinebot

Comment created by @guilhermeblanco:

As of b28fa9a this issue is fixed

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot doctrinebot added this to the 2.5 milestone Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment