Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-3224

getResult(HYDRATE_OBJECT) with joined query is returning reduced number of rows

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 2.4.2
    • Fix Version/s: None
    • Component/s: ORM
    • Security Level: All
    • Labels:
    • Environment:
      osx, PHP-FPM 5.5.13, nginx/1.6.0, mysql 5.6.19 Homebrew

      Description

      given that i have these 2 entities (pseodocode):

      /**
       * @ORM\Table(name="entity1", options={"collate"="utf8_unicode_ci", "charset"="utf8"})
       * @ORM\Entity(repositoryClass="Entity1Repository")
       */
      class Entity1 {
          /**
           * @var integer
           * @ORM\Column(name="id", type="integer")
           * @ORM\Id
           * @ORM\GeneratedValue(strategy="AUTO")
           */
          protected $id;
      
          /**
           * @var string
           * @ORM\Column(name="name", type="string")
           */
          protected $name;
      
          /**
           * @var Entity2[]
           * @ORM\OneToMany(targetEntity="Entity2", mappedBy="entity1")
           */
          protected $entity2;
      }
      
      /**
       * @ORM\Table(name="entity2", options={"collate"="utf8_unicode_ci", "charset"="utf8"})
       * @ORM\Entity()
       */
      class Entity2 {
          /**
           * @var integer
           * @ORM\Column(name="id", type="integer")
           * @ORM\Id
           * @ORM\GeneratedValue(strategy="AUTO")
           */
          protected $id;
      
          /**
           * @var \DateTime
           * @ORM\Column(name="date", type="datetime")
           */
          protected $date;
      
          /**
           * @var Entity1
           * @ORM\ManyToOne(targetEntity="Entity1", inversedBy="entity2", fetch="EAGER")
           */
          protected $entity1;
      }
      

      tables and data

      entity1:

      id name
      1 Jhon
      2 Clare

      entity2:

      id date entity1_id
      1 2011-01-01 00:00:01 1
      2 2012-02-02 00:00:02 1
      3 2013-03-03 00:00:03 2
      4 2014-04-04 00:00:04 2

      my query builder

      use Doctrine\ORM\EntityRepository;
      
      class Entity1Repository extends EntityRepository
      {
          public function getData()
          {
              $qb = $this
                  ->createQueryBuilder('Entity1')
                  ->select('Entity1, Entity2.date')
                  ->join('Entity1.entity2', 'Entity2', Join::WITH, 'Entity2.date > :date')
                  ->setParameter('date', '2000-01-01 00:00:01')
              ;
              $result1 = $qb->getQuery()->getArrayResult(); // HYDRATE_ARRAY
              $result = $qb->getQuery()->getResult(); // HYDRATE_OBJECT
              
      //        return $result1;
      //        return $result2;
          }
      }
      

      proper result is this:

      id name date
      1 Jhon 2011-01-01 00:00:01
      1 Jhon 2012-02-02 00:00:02
      2 Clare 2013-03-03 00:00:03
      2 Clare 2014-04-04 00:00:04

      what is happening

      $result1 = $qb->getQuery()->getArrayResult(); // HYDRATE_ARRAY
      

      is really returning proper number of rows

      BUT and here comes the BUG finally:

      $result2 = $qb->getQuery()->getResult(); // HYDRATE_OBJECT
      

      is returning just 2 rows:

      id name date
      1 Jhon 2011-01-01 00:00:01
      2 Clare 2013-03-03 00:00:03

      this is because somehow entities are made unique.

      my workaround

      as a workaround, what i have to do is, to exectute 2 queries. 1st to get just Entity1.ids joined with Entity2.dates by using `getArrayResult()`
      and second query to get Entity1 objeces by unique ids from 1st query.
      and than manualy join those results in php.

        Activity

        Hide
        Marco Pivetta added a comment -

        I see that you are using Join::WITH, but not providing a conditional in the example. Are you filtering a fetch-joined association?

        Show
        Marco Pivetta added a comment - I see that you are using Join::WITH , but not providing a conditional in the example. Are you filtering a fetch-joined association?
        Hide
        gondo added a comment - - edited

        sorry i've tried to simplify my structure as much as it was possible, there are actually real conditions, one of them is date condition (among many others). i've updated my code

        Show
        gondo added a comment - - edited sorry i've tried to simplify my structure as much as it was possible, there are actually real conditions, one of them is date condition (among many others). i've updated my code
        Hide
        Marco Pivetta added a comment -

        There are still some inconsistencies in the issue - where is that query parameter used, for example?

        Show
        Marco Pivetta added a comment - There are still some inconsistencies in the issue - where is that query parameter used, for example?
        Hide
        gondo added a comment -

        im using it in EntityRepository (sorry, didnt know thats important) i ll update my code
        and the whole code is in Symfony2 project (web and command line applications)

        Show
        gondo added a comment - im using it in EntityRepository (sorry, didnt know thats important) i ll update my code and the whole code is in Symfony2 project (web and command line applications)
        Hide
        Marco Pivetta added a comment -

        What I mean is that in

        ->setParameter('date', new \DateTime('last month'))

        , parameter :date does not exist in the DQL.

        Show
        Marco Pivetta added a comment - What I mean is that in ->setParameter('date', new \DateTime('last month')) , parameter :date does not exist in the DQL.
        Hide
        gondo added a comment -

        i see, sorry its part of JOIN condition, i've updated the code

        Show
        gondo added a comment - i see, sorry its part of JOIN condition, i've updated the code

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            gondo
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated: