Doctrine 2 / MySQL：无法将SQL左连接转换为DQL左连接

Question

So, the goal of my SQL is to get Apartments and some information about them.

This is the SQL:

SELECT apartment.id, AVG(review.staff), COUNT(distinct review.id), city.name as city_name, state.state as state_name,
    MIN(room.price_per_night)
FROM room
    LEFT JOIN apartment ON room.apartment_id=apartment.id
    LEFT JOIN review ON room.apartment_id=review.apartment_id
    LEFT JOIN city ON apartment.city_id = city.id
    LEFT JOIN state ON city.city_state_id = state.id
GROUP BY apartment.id;

which works.

But when I try to turn it to DQL, everything goes fine until I try to join the review table:

    $qb = $em->createQueryBuilder('c');

    $qb->select("MIN(r.price_per_night), rev.id")
       ->from("TechforgeApartmentBundle:Room", 'r')
       ->leftJoin('r.apartment', 'a')
       ->leftJoin('Review', 'rev', \Doctrine\ORM\Query\Expr\Join::WITH, 'r.apartment = rev.apartment')
       ->groupBy('a.id');

It always complains about Review not being identified before:

[Semantical Error] line 0, col 116 near 'rev WITH r.apartment': Error: Identification Variable Review used in join path expression but was not defined before.

I tried alot, and nothing seemed to help.

Answer 1

I solved this by using raw sql queries:

        $em = $this->getDoctrine()->getEntityManager();
        $stmt = $em->getConnection()
                   ->prepare("...
             ");
        $stmt->execute();