doctrine2 select in from

0
votes

How to do this query in Doctrine 2 QueryBuilder:

SELECT AVG(x.distance) avg_distance FROM (SELECT r.* FROM result r WHERE r.place_id = ? GROUP BY r.place_id ORDER BY r.id DESC LIMIT 100

I try this:

$dql = $qb
        ->select('r.*')
        ->from('CoreBundle:Result', 'r')
        ->where('r.place = :place')
        ->orderBy('r.id', 'DESC')
        ->setMaxResults(100)
        ->setParameter('place', $place)
        ->getDQL()
;

$result = $qb
            ->select('AVG(x.distance) avg_distance')
            ->from($dql, 'x')
            ->getQuery()
            ->getArrayResult();

but not work

SELECT r.* FROM': Error: Class 'SELECT' is not defined.
1
phpsymfonydoctrine-ormsubquery

1 Answers

0
votes
$sql = "SELECT AVG(x.distance) avg_distance FROM (SELECT r.* FROM result r WHERE r.place_id = :place_id ORDER BY r.id DESC LIMIT 100) x ";

$stmt = $this->em->getConnection()->prepare($sql);
$stmt->bindValue(':place_id', $place->getId());
$stmt->execute();
return $stmt->fetch();