Doctrine 2 - ORM

Optimize OneToMany and ManyToMany without join

Details

  • Type: New Feature New Feature
  • Status: In Progress In Progress
  • Priority: Major Major
  • Resolution: Unresolved
  • Affects Version/s: Git Master
  • Fix Version/s: None
  • 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

Descending order - Click to sort in ascending order
Hide
Permalink
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
Permalink
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
Permalink
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
Permalink
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

People

  • Assignee:
    Benjamin Eberlei
    Reporter:
    Andrey Kolyshkin
Vote (5)
Watch (6)

Dates

  • Created:
    Updated: