This is related to a previous question here: Doctrine/Symfony query builder add select on left join
I want to perform a complex join query using Doctrine ORM. I want to select 10 paginated blog posts, left joining a single author, like value for current user, and hashtags on the post. My query builder looks like this:
$query = $em->createQueryBuilder()
->select('p')
->from('Post', 'p')
->leftJoin('p.author', 'a')
->leftJoin('p.hashtags', 'h')
->leftJoin('p.likes', 'l', 'WITH', 'l.post_id = p.id AND l.user_id = 10')
->where("p.foo = bar")
->addSelect('a AS post_author')
->addSelect('l AS post_liked')
->addSelect('h AS post_hashtags')
->orderBy('p.time', 'DESC')
->setFirstResult(0)
->setMaxResults(10);
// FAILS - because left joined hashtag collection breaks LIMITS
$result = $query->getQuery()->getResult();
// WORKS - but is extremely slow (count($result) shows over 80,000 rows)
$result = new \Doctrine\ORM\Tools\Pagination\Paginator($query, true);
Strangely, count($result) on the paginator shows the total number of rows in my table (over 80,000) but traversing the $result with foreach outputs 10 Post entities, as expected. Do I need to do some additional configuration to properly limit my paginator?
If this is a limitation of the paginator class what other options do I have? Writing custom paginator code or other paginator libraries?
(bonus): How can I hydrate an array, like $query->getQuery()->getArrayResult();?
EDIT: I left out a stray orderBy in my function. It looks like including both groupBy and orderBy causes the slowdown (using groupBy rather than the paginator). If I omit one or the other, the query is fast. I tried adding an index on the "time" column in my table, but didn't see any improvement.
Things I Tried
// works, but makes the query about 50x slower
$query->groupBy('p.id');
$result = $query->getQuery()->getArrayResult();
// adding an index on the time column (no improvement)
indexes:
time_idx:
columns: [ time ]
// the above two solutions don't work because MySQL ORDER BY
// ignores indexes if GROUP BY is used on a different column
// e.g. "ORDER BY p.time GROUP BY p.id is" slow