Long query time

Question

I have a query to post last 10 tv show episodes by sorting it by date (from newest to oldest) like this:

return $this->getEntityManager()->createQuery('SELECT t FROM AppBundle:TvShow t JOIN t.episodes e ORDER BY e.date DESC')->setFirstResult(0)->setMaxResults(10)->getResult();

It returns only 9 nine episode. We have similar queries in same page too, they are working fine. When i setMaxResults to (11) just then it returns 10 episodes.

Another issue related with this query is: it takes too long compared to other similar queries. (about 200ms)

What do you suggest for me?

Thanks in advance.

What is your actual question? And is `t` a column in one of the tables or do you mean `t.*`? — Gordon Linoff, Feb 21 '16 at 16:56
@GordonLinoff t is a class (table). it is used to get all tv shows. i am using Symfony2 and Doctrine i don't know if it helps. My questions are: why it returns only nine episodes instead of ten?? and why does it take too long? — Kerem Boyalı, Feb 21 '16 at 17:07
Possible duplicate of [Limiting a doctrine query with a fetch-joined collection?](http://stackoverflow.com/questions/5620771/limiting-a-doctrine-query-with-a-fetch-joined-collection) — Paweł Mikołajczuk, Feb 21 '16 at 18:49

score 1 · Answer 1 · answered Feb 21 '16 at 18:47

Like in Richard answer - wrong result with setMaxResults and fetch-joined collection is doctrine normal behaviour.

To make it works you can use Doctrine Pagination (from Doctrine 2.2) (docs: http://docs.doctrine-project.org/en/latest/tutorials/pagination.html)

Example usage:

use Doctrine\ORM\Tools\Pagination\Paginator;

$query->setMaxResults($limit);
$query->setFirstResult($offset);
$results = new Paginator($query, $fetchJoin = true);

Long query time looks like a topic for another question.

score 0 · Answer 2 · answered Feb 21 '16 at 18:40

Straight from the documentation:

If your query contains a fetch-joined collection specifying the result limit methods are not working as you would expect. Set Max Results restricts the number of database result rows, however in the case of fetch-joined collections one root entity might appear in many rows, effectively hydrating less than the specified number of results.

https://doctrine-orm.readthedocs.org/en/latest/

Long query time

2 Answers2