[BNM] CakePHP GROUP BY question

[Alex Bovey]

Hi CakePHP gurus...

I'm trying to suss out the GROUP BY functions in cake and I'm hoping
for a couple of pointers.

In my app I have the following relationship - Vehicle hasMany Image
and Image belongsTo Vehicle.

CREATE TABLE `images` (
 `id` int(11) NOT NULL auto_increment,
 `filename` varchar(255) NOT NULL default '',
 `vehicle_id` int(11) default NULL,
 PRIMARY KEY  (`id`)
);

CREATE TABLE `vehicles` (
 `id` int(11) NOT NULL auto_increment,
 `name` varchar(255) NOT NULL default '',
 PRIMARY KEY  (`id`)
);

What I'd like to do is use GROUP BY to get a complete list of vehicles
with a count of images for that vehicle, even if it's none.

The raw SQL it would be something like this (but this won't take into
account vehicles with no images):

SELECT COUNT(*), vehicles.name FROM images LEFT OUTER JOIN vehicles ON
images.vehicle_id = vehicles.id GROUP BY images.vehicle_id;

Any ideas how to do what I'm trying to achieve with find and group?

Thanks guys,

Alex

--
Please note new telephone number 0844 567 8995

Alex Bovey
Web Developer | Alex Bovey Consultancy Ltd
Registered in England & Wales no. 6471391 | VAT no. 934 8959 65
PHP | MySQL | AJAX | XHTML | CSS | Javascript | XML | W3C Accessibility