[BNM] CakePHP GROUP BY question

[David Pashley]

On Jun 01, 2009 at 15:47, Alex Bovey praised the llamas by saying:
> On Mon, Jun 1, 2009 at 3:34 PM, joseppi c <joseppic at yahoo.co.uk> wrote:
> > The Google Group for cake is a decent resource for this kind of thing:
> > http://groups.google.com/group/cake-php/browse_thread/thread/fc4a738030722857/ccbc1a80cf8a92f9?lnk=gst&q=group#ccbc1a80cf8a92f9
> >
> > IMHO I would advise you to move away from Cake's DB ERM attempt and introduce your own queries as methods inside model classes.
> > Your SQL seems strong enough to avoid the bloated, restricted Cake way.
> >
> > Joe.
> 
> Cheers Joe you're right.
> 
> I'm not sure my SQL is strong enough to work out how to include
> vehicles with a count of 0 with a single query, but then maybe I'd be
> best of doing two queries anyway and merging the results.
> 
> Cheers,
> 
> Alex

Totally untested, but you could use a union query, one to get the ones
with an image, and another to get the ones without any.

SELECT 
   COUNT(*) as count, vehicles.name 
FROM 
   vehicles 
INNER JOIN 
   images ON id = images.vehicles_id 
GROUP BY 
   vehicles.name

UNION 

SELECT 
   0 as count, vehicles.name 
FROM 
   vehicles 
LEFT JOIN 
   images ON id = images.vehicles_id 
WHERE 
   images.vehicle_id is null

-- 
David Pashley
david at davidpashley.com
Nihil curo de ista tua stulta superstitione.