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. #2023

Closed
doctrinebot opened this Issue Sep 30, 2011 · 6 comments

2 participants

@doctrinebot

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 articleid = ? WHERE user_id = ? AND articleid = ?

@doctrinebot

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

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 articletext SET article_id = 1 WHERE locale = 'en' AND articleid = 1
UPDATE articletext SET article_id = 2 WHERE locale = 'en' AND articleid = 2
UPDATE articletext SET article_id = 3 WHERE locale = 'en' AND articleid = 3
UPDATE articletext SET article_id = 4 WHERE locale = 'en' AND articleid = 4
UPDATE articletext SET article_id = 5 WHERE locale = 'en' AND articleid = 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

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

Comment created by @beberlei:

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

@doctrinebot

Issue was closed with resolution "Fixed"

@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot added this to the 2.1.3 milestone Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
@doctrinebot doctrinebot added the Bug label Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment