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

Closed
doctrinebot opened this Issue Jul 23, 2014 · 10 comments

5 participants

@doctrinebot

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.

@doctrinebot

Comment created by @ocramius:

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

@doctrinebot

Comment created by gondo:

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

@doctrinebot

Comment created by @ocramius:

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

@doctrinebot

Comment created by gondo:

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)

@doctrinebot

Comment created by @ocramius:

What I mean is that in ->setParameter('date', new \DateTime('last month')), parameter :date does not exist in the DQL.

@doctrinebot

Comment created by gondo:

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

@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot added the Bug label Dec 7, 2015
@TomasPilar

Hi, i just spent the few hours with debugging my app since I encountered the same bug that describes Gondo. With method getArrayResult() is the result OK but with method getResult() the result contains fewer items.

@Ocramius
Doctrine member

Re-looked at this: seems like expected behavior from my point of view.

@maresja1

Seems like an expected behavior to me too.

@Ocramius Ocramius added Invalid and removed Bug labels Jan 20, 2016
@Ocramius Ocramius assigned Ocramius and unassigned beberlei Jan 20, 2016
@Ocramius
Doctrine member

Closing. Needs to be reproduced by a test, if re-opened.

@Ocramius Ocramius closed this Jan 20, 2016
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment