Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. 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.

    Details

    • Type: Bug Bug
    • Status: 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

        Hide
        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
        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
        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
        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
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: