Get filtered related data without excluding first table rows if there's no related data in second table

Question

Having two related tables, Car and CouponException, I wanna get all cars within an array of models and also get the CouponExceptions related to every Car, but the tricky thing comes here.. i only want to get the CouponException for that car given a coupon id. So what i'm trying now is:

$versions = Doctrine_Query::create()
    ->from('Car c, c.CouponException ce')
    ->whereIn('c.model', $models)
    ->addWhere('ce.coupon_id = ?', $cid)
    ->fetchArray();

But it only returns to me cars with a coupon exception, and what i want is get all cars in a model list and get the CouponException for that car if there is one with a given Coupon id...

Answer 1

I had to use the LEFT JOIN to get all results and use the "with" keyword to filter the second table.

    $versions   = Doctrine_Query::create()
                    ->select('c.*, ce.*')
                    ->from('Car c')
                    ->leftJoin('c.CouponException ce WITH ce.coupon_id = '.$cid)
                    ->whereIn('c.model', $models)
                    ->fetchArray();

Now it works :))))