Okay they can be done with CakePHP. The first thing you need to do is make the "virtual fields" of average_rating and CameraCount using afterFind in your model.
Then, when using FindAll you can pass the order of Category.cameraCount ASC etc. Keep in mind that you can also sort on associated fields by calling them out explicility. $this->ModelName->findAll(null, null, AssociatedModel.fieldname ASC); On Oct 8, 8:42 am, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > Hi there, > > I'm writing an application in CakePHP and everything's going well so > far. > > I've got a slight problem though in trying to order my results when the > order condition is not straightforward. > > I have a list of Categories and a list of Cameras. These are joined by > a hasAndBelongsTo relationship. > The schema is all good and everything works just fine. > > I have a list of categories on my site and display stats such as the > number of cameras in the category > and the average user rating for each of these cameras. > > I want to be able to offer various sorting options on the list of > categories: > > 1) Sort by name (easy using findAll()!) > 2) Sort by number of cameras in category. > 3) Sort by average rating of all cameras in a category. > > The first one is a doddle using a findAll(). > > As far as I can see, I can't use Cake's built-in ActiveRecord-like > functions to do 2) and 3). Am I missing something here?? > > My options for them would seem to be to use query() or to sort the > array in php in the view. The latter option just seems wrong to me as > I believe on getting the DB to do as much of my work for me as I > can!!!! > > Here are the straight SQL statements to get me what I want. Can this > be translated into cake's database functions or am I best to stick with > the straight SQL? > > Any thoughts would be much appreciated, > > Euan. > > For case 2: > > $sql = "SELECT Category.*, COUNT(cameras_categories.camera_id) AS cnt > FROM categories Category > LEFT JOIN cameras_categories ON > (Category.id=cameras_categories.category_id) > GROUP BY Category.id > ORDER BY cnt DESC"; > > For case 3: > $sql = "SELECT Category.*, AVG(Rating.value) AS avg > FROM categories Category > LEFT JOIN cameras_categories ON > (Category.id=cameras_categories.category_id) > LEFT JOIN cameras Camera ON > (cameras_categories.camera_id=Camera.id) > LEFT JOIN ratings Rating ON (Camera.id=Rating.camera_id) > GROUP BY Category.id > ORDER BY avg DESC"; > > Schema for the relevant tables: > > ql> describe categories;describe cameras;describe camerast_categories; > +-------------------+------------------+------+-----+---------+----------------+ > | Field | Type | Null | Key | Default | Extra > | > +-------------------+------------------+------+-----+---------+----------------+ > | id | int(10) unsigned | | PRI | NULL | > auto_increment | > | category_name | varchar(50) | | UNI | | > | > | category_desc | text | YES | | NULL | > | > | category_keywords | text | YES | | NULL | > | > | sort_order | int(4) | YES | | 0 | > | > | created | datetime | YES | | NULL | > | > | modified | datetime | YES | | NULL | > | > +-------------------+------------------+------+-----+---------+----------------+ > 7 rows in set (0.00 sec) > > +---------------+------------------+------+-----+-----------+----------------+ > | Field | Type | Null | Key | Default | Extra > | > +---------------+------------------+------+-----+-----------+----------------+ > | id | int(10) unsigned | | PRI | NULL | > auto_increment | > | url_name | varchar(40) | | UNI | | > | > | title | varchar(40) | | | | > | > | img_path | varchar(150) | | | | > | > | short_desc | varchar(150) | YES | | NULL | > | > | long_desc | text | YES | | NULL | > | > | longitude | decimal(12,7) | | | 0.0000000 | > | > | latitude | decimal(12,7) | | | 0.0000000 | > | > | is_ptz | tinyint(1) | YES | | NULL | > | > | is_relay | tinyint(1) | YES | | NULL | > | > | status | tinyint(1) | YES | | NULL | > | > | width | int(4) | YES | | NULL | > | > | height | int(4) | YES | | NULL | > | > | client_name | varchar(75) | YES | | NULL | > | > | client_url | varchar(150) | YES | | NULL | > | > | client_web | varchar(150) | YES | | NULL | > | > | created | datetime | YES | | NULL | > | > | modified | datetime | YES | | NULL | > | > | meta_keywords | varchar(150) | YES | | NULL | > | > +---------------+------------------+------+-----+-----------+----------------+ > 19 rows in set (0.00 sec) > > mysql> describe cameras_categories; > +-------------+------------------+------+-----+---------+----------------+ > | Field | Type | Null | Key | Default | Extra > | > +-------------+------------------+------+-----+---------+----------------+ > | id | int(10) unsigned | | PRI | NULL | > auto_increment | > | camera_id | int(10) unsigned | | MUL | 0 | > | > | category_id | int(10) unsigned | | MUL | 0 | > | > | created | datetime | YES | | NULL | > | > | modified | datetime | YES | | NULL | > | > +-------------+------------------+------+-----+---------+----------------+ > 5 rows in set (0.00 sec) > > mysql> describe ratings; > +------------+------------------+------+-----+------------+----------------+ > | Field | Type | Null | Key | Default | Extra > | > +------------+------------------+------+-----+------------+----------------+ > | id | int(10) unsigned | | PRI | NULL | > auto_increment | > | camera_id | int(10) unsigned | | MUL | 0 | > | > | value | int(2) | | | 0 | > | > | date | date | | | 0000-00-00 | > | > | ip_address | varchar(20) | YES | | NULL | > | > | created | datetime | YES | | NULL | > | > | modified | datetime | YES | | NULL | > | > +------------+------------------+------+-----+------------+----------------+ > 7 rows in set (0.00 sec) --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Cake PHP" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php -~----------~----~----~----~------~----~------~--~---
