CODEWITHSUNDEEP
phpmysqldoctrine-orm
Tool
Tool

Reputation: 12488

Doctrine 2 / MySQL: having trouble turning SQL left joins to DQL left joins

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.

Upvotes: 0

Views: 1013

Answers (2)

Tool
Tool

Reputation: 12488

I solved this by using raw sql queries: 

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

Upvotes: 0

cantera
cantera

Reputation: 24985

I'm pretty sure you can leave off the WITH condition, assuming Apartment and Review have a mapped association.

$qb->select('MIN(r.price_per_night), rev.id')
   ->from('TechforgeApartmentBundle:Room', 'r')
   ->leftJoin('r.apartment', 'a')
   ->leftJoin('a.review', 'rev')
   ->groupBy('a.id');

If for some reason you do need the WITH condition, the syntax looks like this:

->leftJoin('a.review', 'rev', 'WITH', 'a.id = rev.apartment_id')

Upvotes: 2

Related Questions