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

New Issue

Closed

opened 2026-01-22 13:14:40 +01:00 by admin · 11 comments

admin commented 2026-01-22 13:14:40 +01:00

Owner

Copy Link

Originally created by @doctrinebot on GitHub (May 12, 2011).

Originally assigned to: @guilhermeblanco on GitHub.

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)

Originally created by @doctrinebot on GitHub (May 12, 2011). Originally assigned to: @guilhermeblanco on GitHub. 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) ```

admin added the New Feature label 2026-01-22 13:14:40 +01:00

admin closed this issue 2026-01-22 13:14:40 +01:00

admin commented 2026-01-22 13:14:41 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (May 12, 2011):

Comment created by @beberlei:

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

@doctrinebot commented on GitHub (May 12, 2011): Comment created by @beberlei: Sure you are on git master? this should be optimized already with fetch=EAGER

admin commented 2026-01-22 13:14:43 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (May 12, 2011):

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 commented on GitHub (May 12, 2011): 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 } ```

admin commented 2026-01-22 13:14:44 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (Oct 10, 2011):

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 commented on GitHub (Oct 10, 2011): 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. =)

admin commented 2026-01-22 13:14:45 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (Mar 25, 2013):

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 commented on GitHub (Mar 25, 2013): 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

admin commented 2026-01-22 13:14:46 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (Aug 4, 2013):

Comment created by koc:

Please fix this issue

@doctrinebot commented on GitHub (Aug 4, 2013): Comment created by koc: Please fix this issue

admin commented 2026-01-22 13:14:47 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (Nov 14, 2013):

Comment created by madhkrish:

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

@doctrinebot commented on GitHub (Nov 14, 2013): Comment created by madhkrish: Is this likely to be resolved soon? Or is there a good workaround that we could implement?

admin commented 2026-01-22 13:14:47 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (Nov 28, 2013):

Comment created by col:

Any news on this issue?

@doctrinebot commented on GitHub (Nov 28, 2013): Comment created by col: Any news on this issue?

admin commented 2026-01-22 13:14:47 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (Jan 17, 2014):

Comment created by flip101:

(y) sounds very useful !

@doctrinebot commented on GitHub (Jan 17, 2014): Comment created by flip101: (y) sounds very useful !

admin commented 2026-01-22 13:14:48 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (Apr 16, 2014):

Comment created by @guilhermeblanco:

As of b28fa9a05a this issue is fixed

@doctrinebot commented on GitHub (Apr 16, 2014): Comment created by @guilhermeblanco: As of https://github.com/doctrine/doctrine2/commit/b28fa9a05a868d42c9b161cda3c73a8c5822acb4 this issue is fixed

admin commented 2026-01-22 13:14:49 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (Apr 16, 2014):

Issue was closed with resolution "Fixed"

@doctrinebot commented on GitHub (Apr 16, 2014): Issue was closed with resolution "Fixed"

admin commented 2026-01-22 13:14:49 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (Dec 13, 2015):

Imported 1 attachments from Jira into https://gist.github.com/42476e78f6e5a04da901

• 10991_testDoctrine.php

@doctrinebot commented on GitHub (Dec 13, 2015): Imported 1 attachments from Jira into https://gist.github.com/42476e78f6e5a04da901 - [10991_testDoctrine.php](https://gist.github.com/42476e78f6e5a04da901#file-10991_testDoctrine-php)

admin referenced this issue 2026-01-22 16:04:31 +01:00

[PR #1446] Update batch-processing.rst to add persist() call #9503

Sign in to join this conversation.

No Branch/Tag Specified

Branches Tags

3.6.x

2.20.x

dependabot/github_actions/2.20.x/doctrine/dot-github/dot-github/workflows/composer-lint.yml-14.0.0

3.7.x

4.0.x

2.21.x

old-prototype-3.x

3.6.2

3.6.1

3.6.0

2.20.9

3.5.8

3.5.7

3.5.6

3.5.5

2.20.8

3.5.4

3.5.3

2.20.7

3.5.2

2.20.6

3.5.1

3.5.0

3.4.4

3.4.3

3.4.2

2.20.5

3.4.1

3.4.0

3.3.4

2.20.4

3.3.3

2.20.3

3.3.2

2.20.2

3.3.1

2.20.1

3.3.0

3.2.3

2.20.0

2.19.8

3.2.2

2.19.7

3.2.1

2.19.6

3.2.0

3.1.4

3.1.3

2.19.5

3.1.2

2.19.4

3.1.1

2.19.3

2.19.2

2.19.1

3.1.0

2.19.0

3.0.3

2.18.3

3.0.2

2.18.2

3.0.1

2.18.1

3.0.0

2.18.0

2.17.5

3.0.0-RC1

2.17.4

2.17.3

2.17.2

2.17.1

3.0.0-beta2

2.17.0

2.16.3

3.0.0-beta1

2.16.2

2.16.1

2.16.0

2.15.5

2.15.4

2.15.3

2.15.2

2.15.1

2.15.0

2.14.3

2.14.2

2.14.1

2.14.0

2.13.5

2.13.4

2.13.3

2.13.2

2.13.1

2.13.0

2.12.4

2.12.3

2.12.2

2.12.1

2.12.0

2.11.3

2.11.2

2.11.1

2.11.0

2.10.5

2.10.4

2.10.3

2.10.2

2.10.1

2.10.0

2.9.6

2.9.5

2.9.4

2.9.3

2.9.2

2.9.1

2.9.0

2.8.5

2.8.4

2.8.3

2.8.2

2.8.1

2.8.0

2.7.5

2.7.4

v2.7.3

v2.7.2

v2.7.1

v2.7.0

v2.6.6

v2.6.5

v2.6.4

v2.6.3

v2.6.2

v2.6.1

v2.6.0

v2.5.14

v2.5.13

v2.5.12

v2.5.11

v2.5.10

v2.5.9

v2.5.8

v2.5.7

v2.5.6

v2.5.5

v2.5.4

v2.5.3

v2.5.2

v2.4.8

v2.5.1

v2.5.0

v2.5.0-RC2

v2.5.0-RC1

v2.5.0-beta1

v2.5.0-alpha2

v2.5.0-alpha1

v2.4.7

v2.4.6

v2.4.5

v2.4.4

v2.4.3

v2.3.6

v2.4.2

2.3.5

v2.4.1

v2.4.0

2.4.0-RC2

2.4.0-RC1

2.4.0-BETA2

2.3.4

2.3.3

2.4.0-BETA1

2.3.2

2.3.1

2.3.0

2.3.0-RC4

2.3.0-RC3

2.3.0-RC2

2.2.3

2.3.0-RC1

2.3.0-BETA1

2.1.7

2.2.2

2.2.1

2.1.6

2.2.0

2.2.0-RC1

2.2.0-BETA2

2.2.0-BETA1

2.1.5

2.1.4

2.1.3

2.1.2

2.1.1

2.0.7

2.1.0

2.1.0RC3

2.1.0RC2

2.1.0RC1

2.0.6

2.1.0BETA1

2.0.5

2.0.4

2.0.3

2.0.2

2.0.1

2.0.0

2.0.0RC2

2.0.0RC1

2.0.0-BETA4

2.0.0-BETA3

2.0.0-BETA2

2.0.0-BETA1

Labels

Clear labels

BC Break

Bug

Can't Fix

Deprecation

Documentation

DQL

Duplicate

Failing Test

Hacktoberfest

Improvement

Improvement

Incomplete

Invalid

Missing Tests

New Feature

PR Created

pull-request

Mirrored from GitHub Pull Request

Question

Regression

Status: Waiting feedback

Waiting feedback

WIP

Won't Fix

No Label New Feature

Milestone

No items

No Milestone

Projects

Clear projects

No project

Assignees

Clear assignees

admin

No Assignees

1 Participants

Notifications

Subscribe

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#1446