0
\$\begingroup\$

I'm wondering if this is the right way to have a subquery in Doctrine. I have two entities, one called User and one called Log. There's a mapping from Log to User but not the other way around (ManyToOne).

I need a list of all users, which had log entries within a certain period. This is my current solution:

// UserRepository.php

/**
 * @return User[]
 */
public function findLogUsersByCategory(int $category, \DateTime $start, \DateTime $end): array
{
return $this->createQueryBuilder('e')
    ->where('e.id IN (:users)')
    ->setParameter('users', $this->_em->createQueryBuilder()
        ->select('u.id')
        ->from(Log::class, 'l')
        ->join('l.user', 'u')
        ->where('l.category = :category')
        ->setParameter('category', $category)
        ->andWhere('l.created BETWEEN :start AND :end')
        ->setParameter('start', $start)
        ->setParameter('end', $end)
        ->orderBy('l.category', 'ASC')
        ->addOrderBy('l.id', 'ASC')
        ->groupBy('u.id')
        ->getQuery()->getResult()
    )
    ->getQuery()
    ->getResult();
}

The query works like this, however I don't like, that it will execute 2 queries (first the inner, write result to memory in PHP and use it as parameter for the outer query). I've tried to use ->getDQL() instead of the ->getQuery()->getResult() for the inner query, but then I don't get any results (I'm guessing the parameters for the inner query aren't processed when calling getDQL()).

So the question would be, how to avoid having PHP temporary store the result of the inner query and instead let the DB server work it's magic?

\$\endgroup\$
  • \$\begingroup\$ Can you provide the actual sql using ->getSql() method \$\endgroup\$ – Dan Jan 28 at 12:30

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.