orm: DDC-3224: getResult(HYDRATE_OBJECT) with joined query is returning reduced number of rows

Jira issue originally created by user gondo:

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.

About this issue

Original URL
State: closed
Created 10 years ago
Comments: 16 (4 by maintainers)

Most upvoted comments

@maresja1 thanks for taking time to write the response. i will certainly read the linked article. but this behaviour is counter-intuitive and without studying the wana be expected behaviour, one gets confused easily.

gondo on Nov 28, 2016

@gondo Because of how hydratation works - @Ocramius will correct me, if I’m wrong - but when you’re hydratating objects (getResult()) you don’t expect the same instance to be returned twice, thus when you join related table (let’s call it B), having more rows linked to the same instance of parent object (call it A), Doctrine “groups” the repeating values of an instance of A, so you get each instance from A just once.

The related instances of class B are hydratated separately and that is why from one query you can get one instance of A having hydrateted collection of multiple instances of B.

This is nicely described (with the connected performance drawbacks) in this article written by @Ocramius: https://ocramius.github.io/blog/doctrine-orm-optimization-hydration/

maresja1 on Nov 28, 2016