symfony2 / doctrine2 - dql statement onetoMany

Question

i have an entity driver and an entity car. One driver has many cars. I have the following dql query:

$query = $this->getEntityManager()->createQuery('select d, c FROM driver d JOIN d.cars c WHERE c.color=:color');                
$query->setParameter('color', $color);
$query->setFirstResult((int)$offset ? $offset*15 : 0 );
$query->setMaxResults(15);

The resultlist has 5 rows. But when i perform the created sql query in the console, then i have 15 rows. When i change the join type to left join, the rows are count by car not by driver. But i'm using a pager for read the drivers who have red cars.

Does anybody know what i'm doing wrong? Thank you very much.

Maxence Maxence · Accepted Answer · 2012-03-18T20:17:55

When you are doing fetch join queries with Doctrine, there is more than one row returned for each entity. If you use setMaxResults on these queries, some entities are discarded.

Read this: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#first-and-max-result-items-dql-query-only

The solution is here: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/pagination.html

symfony2 / doctrine2 - dql statement onetoMany

1 Answers