[BNM] CakePHP GROUP BY question

[joseppi c]

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.




________________________________
From: Alex Bovey <alex at bovey.co.uk>
To: Brighton New Media <bnmlist at brightonnewmedia.org>
Sent: Monday, 1 June, 2009 14:53:00
Subject: [BNM] CakePHP GROUP BY question

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
-- 

BNM Subscribe/Unsubscribe:
http://www.brightonnewmedia.org/options/bnmlist

BNM powered by Wessex Networks:
http://www.wessexnetworks.com