DDC-1400: joining and selecting associated collection, which is using indexBy, to a query is triggering UPDATE queries for each collection element which were joined. #1751

New Issue

admin · 2026-01-22T13:24:35+01:00

admin commented

2026-01-22 13:24:35 +01:00

Originally created by @doctrinebot on GitHub (Sep 30, 2011).

Originally assigned to: @beberlei on GitHub.

Jira issue originally created by user hypno:

/****
 * @Entity
 */
class Article
{

    /****
     * @Id
     * @Column(type="integer")
     */
    protected $id;

    /****
     * @OneToMany(targetEntity="UserState", mappedBy="article", indexBy="userId", fetch="EXTRA_LAZY")
     */
    protected $userStates;

    .......

}

/****
 * @Entity
 */
class User
{

    /****
     * @Id
     * @Column(type="integer")
     */
    protected $id;

    /****
     * @OneToMany(targetEntity="UserState", mappedBy="user", indexBy="articleId", fetch="EXTRA_LAZY")
     */
    protected $userStates;

    .......

}

/****
 * @Entity
 */
class UserState
{

    /****
      * @Id
     *  @ManyToOne(targetEntity="Article", inversedBy="userStates")
     */
    protected $article;

    /****
      * @Id
     *  @ManyToOne(targetEntity="User", inversedBy="userStates")
     */
    protected $user;

    /****
     * @Column(name="user_id", type="integer")
     */
    protected $userId;

    /****
     * @Column(name="article_id", type="integer")
     */
    protected $articleId;

    /****
     * @Column(type="boolean")
     */
    protected $hasLiked;

    .......

}

$q = $em->createQuery("SELECT a, s FROM Article a JOIN a.userStates s WITH s.user = :activeUser");
$q->setParameter('activeUser', $activeUserId);
$q->getResult();

if i $em->flush() now it will execute lots of update queries like:
UPDATE userstate SET article_id = ? WHERE user_id = ? AND article_id = ?

Originally created by @doctrinebot on GitHub (Sep 30, 2011). Originally assigned to: @beberlei on GitHub. Jira issue originally created by user hypno: ``` /**** * @Entity */ class Article { /**** * @Id * @Column(type="integer") */ protected $id; /**** * @OneToMany(targetEntity="UserState", mappedBy="article", indexBy="userId", fetch="EXTRA_LAZY") */ protected $userStates; ....... } /**** * @Entity */ class User { /**** * @Id * @Column(type="integer") */ protected $id; /**** * @OneToMany(targetEntity="UserState", mappedBy="user", indexBy="articleId", fetch="EXTRA_LAZY") */ protected $userStates; ....... } /**** * @Entity */ class UserState { /**** * @Id * @ManyToOne(targetEntity="Article", inversedBy="userStates") */ protected $article; /**** * @Id * @ManyToOne(targetEntity="User", inversedBy="userStates") */ protected $user; /**** * @Column(name="user_id", type="integer") */ protected $userId; /**** * @Column(name="article_id", type="integer") */ protected $articleId; /**** * @Column(type="boolean") */ protected $hasLiked; ....... } $q = $em->createQuery("SELECT a, s FROM Article a JOIN a.userStates s WITH s.user = :activeUser"); $q->setParameter('activeUser', $activeUserId); $q->getResult(); ``` if i $em->flush() now it will execute lots of update queries like: UPDATE userstate SET article_id = ? WHERE user_id = ? AND article_id = ?

admin added the Bug label 2026-01-22 13:24:35 +01:00

admin closed this issue

2026-01-22 13:24:37 +01:00

admin commented

2026-01-22 13:24:38 +01:00

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

Comment created by @beberlei:

I think your mapping is wrong. You cannot map an @Id + @ManyToOne and then remap the same column using @Column.

What exactly are the parameters to the query? How is it updating article_id ?

Is this even affected by "indexBy"? Can you remove them and try again?

@doctrinebot commented on GitHub (Oct 15, 2011): Comment created by @beberlei: I think your mapping is wrong. You cannot map an @Id + @ManyToOne and then remap the same column using @Column. What exactly are the parameters to the query? How is it updating article_id ? Is this even affected by "indexBy"? Can you remove them and try again?

admin commented

2026-01-22 13:24:38 +01:00

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

Comment created by hypno:

I have to use @Id + $ManyToOne and @Column on the same database field because indexBy only supports normal @Columns as key. We have used this workaround numerous times in our system without any issues.

And it seems that this is not causing the updates. Here is another example:

/****
 * @Entity
 */
class Article
{

    /****
     * @Id
     * @Column(type="integer")
     */
    protected $id;

    /****
     * @OneToMany(targetEntity="ArticleText", mappedBy="article", indexBy="locale", fetch="EXTRA_LAZY")
     */
    protected $texts;

    .......

}

/****
 * @Entity
 */
class ArticleText
{

    /****
     * @Id
     * @ManyToOne(targetEntity="Article", inversedBy="texts")
     */
    protected $article;

    /****
     * @Id
     * @Column
     */
    protected $locale;

   /****
    * @Column
    */
   protected $title;

    .......

}

$q = $em->createQuery("SELECT a, t FROM Article a JOIN a.texts t WITH t.locale = :activeLocale");
$q->setParameter('activeLocale', 'en');

$em->flush(); // dummy flush, no queries made

$q->getResult();

$em->flush(); // this will trigger the updates

UPDATE article_text SET article_id = 1 WHERE locale = 'en' AND article_id = 1
UPDATE article_text SET article_id = 2 WHERE locale = 'en' AND article_id = 2
UPDATE article_text SET article_id = 3 WHERE locale = 'en' AND article_id = 3
UPDATE article_text SET article_id = 4 WHERE locale = 'en' AND article_id = 4
UPDATE article_text SET article_id = 5 WHERE locale = 'en' AND article_id = 5
.......

it does it one per article for all articles in result.

If i remove the indexBy annotation the updates disappear. Extra lazy loading has no effect on this bug.

@doctrinebot commented on GitHub (Oct 17, 2011): Comment created by hypno: I have to use @Id + $ManyToOne and @Column on the same database field because indexBy only supports normal @Columns as key. We have used this workaround numerous times in our system without any issues. And it seems that this is not causing the updates. Here is another example: ``` /**** * @Entity */ class Article { /**** * @Id * @Column(type="integer") */ protected $id; /**** * @OneToMany(targetEntity="ArticleText", mappedBy="article", indexBy="locale", fetch="EXTRA_LAZY") */ protected $texts; ....... } /**** * @Entity */ class ArticleText { /**** * @Id * @ManyToOne(targetEntity="Article", inversedBy="texts") */ protected $article; /**** * @Id * @Column */ protected $locale; /**** * @Column */ protected $title; ....... } $q = $em->createQuery("SELECT a, t FROM Article a JOIN a.texts t WITH t.locale = :activeLocale"); $q->setParameter('activeLocale', 'en'); $em->flush(); // dummy flush, no queries made $q->getResult(); $em->flush(); // this will trigger the updates ``` UPDATE article_text SET article_id = 1 WHERE locale = 'en' AND article_id = 1 UPDATE article_text SET article_id = 2 WHERE locale = 'en' AND article_id = 2 UPDATE article_text SET article_id = 3 WHERE locale = 'en' AND article_id = 3 UPDATE article_text SET article_id = 4 WHERE locale = 'en' AND article_id = 4 UPDATE article_text SET article_id = 5 WHERE locale = 'en' AND article_id = 5 ....... it does it one per article for all articles in result. If i remove the indexBy annotation the updates disappear. Extra lazy loading has no effect on this bug.

admin commented

2026-01-22 13:24:39 +01:00

@doctrinebot commented on GitHub (Nov 19, 2011):

Comment created by @beberlei:

Attached is a working testcase with your example code.

Can you please verify that it follows your example exactly and try to make it generate those UPDATEs?

@doctrinebot commented on GitHub (Nov 19, 2011): Comment created by @beberlei: Attached is a working testcase with your example code. Can you please verify that it follows your example exactly and try to make it generate those UPDATEs?

admin commented

2026-01-22 13:24:39 +01:00

@doctrinebot commented on GitHub (Jan 2, 2012):

Comment created by @beberlei:

This is fixed in 2.1.3, it was a bug until 2.1.2

@doctrinebot commented on GitHub (Jan 2, 2012): Comment created by @beberlei: This is fixed in 2.1.3, it was a bug until 2.1.2

admin commented

2026-01-22 13:24:39 +01:00

@doctrinebot commented on GitHub (Jan 2, 2012):

Issue was closed with resolution "Fixed"

@doctrinebot commented on GitHub (Jan 2, 2012): Issue was closed with resolution "Fixed"

admin commented

2026-01-22 13:24:40 +01:00

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

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

11113_DDC1400Test.php

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

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#1751