Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1595

Wrong count in relation with inheritance

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Invalid
    • Affects Version/s: Git Master
    • Fix Version/s: None
    • Component/s: ORM
    • Security Level: All
    • Labels:
      None

      Description

      I have some code like this:

      Resource.php
      /**
       * @Orm\Entity
       * @Orm\Table(name="resource")
       * @Orm\InheritanceType("SINGLE_TABLE")
       * @Orm\DiscriminatorColumn(name="type", type="string")
       * @Orm\DiscriminatorMap({"Container" = "Container", "News"="News"})
       */
      abstract class Resource
      {
      }
      
      Container.php
      /**
       * @Orm\Entity
       */
      class Container extends Resource
      {
          /**
           * @Orm\ManyToMany(targetEntity="News", fetch="EXTRA_LAZY")
           * @Orm\JoinTable(name="resource_path",
           *      joinColumns={@Orm\JoinColumn(name="parent", referencedColumnName="id")},
           *      inverseJoinColumns={@Orm\JoinColumn(name="item", referencedColumnName="id")}
           *      )
           */
          protected $news;
      
          public function getNews()
          {
              return $this->news;
          }
      }
      
      News.php
      /**
       * @Orm\Entity
       */
      class News extends Resource
      {
      }
      
      $container = $repository->find($id);
      $news = $container->getNews();
      
      
      $news->getValues(); 
      /* generates SQL like this: 
      *  SELECT * FROM resource t0
      *  INNER JOIN resource_path ON t0.id = resource_path.item 
      *  WHERE resource_path.parent = ? AND t0.type IN ('News') 
      */
      
      $news->count(); 
      /* generates SQL like this: 
      *  SELECT COUNT(*) FROM resource_path t WHERE parent = ? 
      *
      * Expected SQL like this:
      *  SELECT count(*) FROM resource t0
      *  INNER JOIN resource_path ON t0.id = resource_path.item
      *  WHERE resource_path.parent = ? AND t0.type IN ('News')
      */
      
      
      

        Issue Links

          Activity

          Hide
          Guilherme Blanco added a comment -

          How can this be returning you incorrect values for count?

          The second query retrieves based on the join table, which is supposed to have only the linking between Container and News.
          It is expected that given a Container ID, all you want to know is the actual number of associated News, which a simple check to join table is enough, no matter which inheritance strategy you are looking into.

          Marking ticket as invalid.
          Provide better information and explain how it is affecting you if my explanation is not enough.

          Show
          Guilherme Blanco added a comment - How can this be returning you incorrect values for count? The second query retrieves based on the join table, which is supposed to have only the linking between Container and News. It is expected that given a Container ID, all you want to know is the actual number of associated News, which a simple check to join table is enough, no matter which inheritance strategy you are looking into. Marking ticket as invalid. Provide better information and explain how it is affecting you if my explanation is not enough.
          Hide
          Gandzy Ghennady added a comment -

          Thank you for your comprehensive answer. My fault, I may not be correctly described the problem.

          As I understood from you answer, the following code is not correct

          Container.php
          /**
           * @Orm\Entity
           */
          class Container extends Resource
          {
              /**
               * @Orm\ManyToMany(targetEntity="News", fetch="EXTRA_LAZY")
               * @Orm\JoinTable(name="resource_path",
               *      joinColumns={@Orm\JoinColumn(name="parent", referencedColumnName="id")},
               *      inverseJoinColumns={@Orm\JoinColumn(name="item", referencedColumnName="id")}
               *      )
               */
              protected $news;
          
              public function getNews()
              {
                  return $this->news;
              }
          
              /**
               * @Orm\ManyToMany(targetEntity="Container", fetch="EXTRA_LAZY")
               * @Orm\JoinTable(name="resource_path",
               *      joinColumns={@Orm\JoinColumn(name="parent", referencedColumnName="id")},
               *      inverseJoinColumns={@Orm\JoinColumn(name="item", referencedColumnName="id")}
               *      )
               */
              protected $containers;
          
              public function getContainers()
              {
                  return $this->containers;
              }
          
          }
          

          in other words in the table resource_path can not be at the same time records linking Container with Container and Container with News.

          Show
          Gandzy Ghennady added a comment - Thank you for your comprehensive answer. My fault, I may not be correctly described the problem. As I understood from you answer, the following code is not correct Container.php /** * @Orm\Entity */ class Container extends Resource { /** * @Orm\ManyToMany(targetEntity= "News" , fetch= "EXTRA_LAZY" ) * @Orm\JoinTable(name= "resource_path" , * joinColumns={@Orm\JoinColumn(name= "parent" , referencedColumnName= "id" )}, * inverseJoinColumns={@Orm\JoinColumn(name= "item" , referencedColumnName= "id" )} * ) */ protected $news; public function getNews() { return $ this ->news; } /** * @Orm\ManyToMany(targetEntity= "Container" , fetch= "EXTRA_LAZY" ) * @Orm\JoinTable(name= "resource_path" , * joinColumns={@Orm\JoinColumn(name= "parent" , referencedColumnName= "id" )}, * inverseJoinColumns={@Orm\JoinColumn(name= "item" , referencedColumnName= "id" )} * ) */ protected $containers; public function getContainers() { return $ this ->containers; } } in other words in the table resource_path can not be at the same time records linking Container with Container and Container with News.
          Hide
          Alex Pogodin added a comment - - edited

          The problem here is that resource_path knows nothing about type of the resource. At the same time, news collection contains only objects of resource.type == 'news'. That means, that if there' not only news inside container, but, probably another container(s), the $news->count() will return incorrect value (the sum of news and containers counts). That's the point.

          Show
          Alex Pogodin added a comment - - edited The problem here is that resource_path knows nothing about type of the resource. At the same time, news collection contains only objects of resource.type == 'news' . That means, that if there' not only news inside container, but, probably another container(s), the $news->count() will return incorrect value (the sum of news and containers counts). That's the point.
          Hide
          Justin A added a comment -

          Should this:

          generates SQL like this: 
          SELECT COUNT(*) FROM resource_path t WHERE parent = ? 
          

          be:

          generates SQL like this: 
          SELECT COUNT(*) FROM resource_path t WHERE t.parent = ? 
          

          Looks like parent columns is missing alias. Is that correct?

          Show
          Justin A added a comment - Should this: generates SQL like this : SELECT COUNT(*) FROM resource_path t WHERE parent = ? be: generates SQL like this : SELECT COUNT(*) FROM resource_path t WHERE t.parent = ? Looks like parent columns is missing alias. Is that correct?
          Hide
          Oleg Namaka added a comment -
          Show
          Oleg Namaka added a comment - Similar issue is described in http://www.doctrine-project.org/jira/browse/DDC-3104

            People

            • Assignee:
              Guilherme Blanco
              Reporter:
              Gandzy Ghennady
            • Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: