Doctrine 2 - ORM

joining and selecting associated collection, which is using indexBy, to a query is triggering UPDATE queries for each collection element which were joined.

Details

  • Type: Bug Bug
  • Status: Resolved Resolved
  • Priority: Major Major
  • Resolution: Fixed
  • Affects Version/s: 2.1.2
  • Fix Version/s: 2.1.3
  • Component/s: None
  • Security Level: All
  • Labels:
    None

Description

 
/**
 * @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 = ?

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Benjamin Eberlei added a comment -

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?

Show
Benjamin Eberlei added a comment - 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?
Hide
Permalink
Reio Piller added a comment - - edited

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.

Show
Reio Piller added a comment - - edited 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.
Hide
Permalink
Benjamin Eberlei added a comment -

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?

Show
Benjamin Eberlei added a comment - 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?
Hide
Permalink
Benjamin Eberlei added a comment -

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

Show
Benjamin Eberlei added a comment - This is fixed in 2.1.3, it was a bug until 2.1.2

People

  • Assignee:
    Benjamin Eberlei
    Reporter:
    Reio Piller
Vote (0)
Watch (1)

Dates

  • Created:
    Updated:
    Resolved: