My apologies. Yes, I am using Cake 1.2 RC1 - I had baked with a nightly prior to the release of RC1 and was building off those queries as a reference point. I've changed the find type from 'list' to 'all' (after reading much more carefully over the API that I had looked at before you quoted it), modified the array syntax of the find call as suggested, and used the new 'group' parameter for GROUP BY clauses. It seems to be working - I will need to add some more values to the database to verify, but it looked like the issue was my forceful attempt at escaping the fields for Cake, and the color coding in my text editor (Zend Eclipse IDE) showing backtick enclosed fields as the same color as single-quote enclosed fields.
Thank you for looking it over and offering your help, Dave! The final solution is posted at the following URL in the bin: http://bin.cakephp.org/saved/33573 ...in case anyone else was curious. The updated CakePHP Vote Controller code (with no change in SQL query, except for the "WHERE 1=1 AND GROUP BY" part is) is below: http://bin.cakephp.org/saved/33571 I'm completely puzzled as to why it's not SELECTing all of my specified fields. :( On Jun 13, 11:35 am, DaveMahon <[EMAIL PROTECTED]> wrote: > Your code looks odd... I'm going to respond assuming that you're > playing with Cake 1.2 RC1. > > Cake handles the escaping of table and field names normally and > expects to see them in the format array('Week.season_id =' => > $curSeason). > Order operators are expected in the format array('points' => 'desc'). > > Also, from the API documentation for find('list'): > * If no fields are specified, then 'id' is used for key and > 'model->displayField' is used for value. > > * If a single field is specified, 'id' is used for key and > specified field is used for value. > * If three fields are specified, they are used (in order) for key, > value and group. > * Otherwise, first and second fields are used for key and value. > > On Jun 13, 10:52 am, BrendonKoz <[EMAIL PROTECTED]> wrote: > > > I'm trying to build a silly test application for personal use before I > > use this for work. For background, I am attempting to grab my > > necessary data from 4 separate tables. I am tallying the weekly > > rating of multiple teams (squads) of the "current week", from the > > current season. My tables' necessary fields for the query and joins > > are listed below (all unnecessary fields have been removed for the > > example): > > > -Seasons > > id > > > -Weeks > > created, season_id > > > -Squads > > id, name, active > > > -Votes > > id, squad_id, week_id, rating > > > A season hasMany weeks. > > A week hasMany votes > > A squad hasMany votes > > A vote belongsTo a squad and a week. > > > Unfortunately, I'm not all that great at creating queries with > > multiple joins. I believe the following query is what I'm *trying* to > > create within CakePHP: > > SELECT s.name, v.squad_id, IFNULL(SUM(v.rating),0) AS points > > FROM squads as s > > LEFT OUTER JOIN votes as v > > ON v.squad_id = s.id > > LEFT JOIN weeks as w > > ON v.week_id = w.id > > LEFT JOIN seasons > > ON w.season_id = seasons.id > > WHERE s.active=1 AND w.created < '2008-06-12 00:00:00' AND > > seasons.id=1 > > GROUP BY s.id > > ORDER BY points DESC, s.name ASC; > > > The code I'm attempting to write is currently in my vote controller, > > and I will probably move it to my model once I get this working, but > > for now... The code is pasted in the > > bin:http://bin.cakephp.org/view/1728865259 > > > The SQL that Cake is generating doesn't seem to have a LEFT OUTER > > JOIN, but perhaps Cake is just smarter than me that way. However, > > it's not SELECTing the fields that I've specified for it to return > > within the find and am completely confused as to why. The SQL Cake > > creates is below: > > SELECT IFNULL(SUM(`Vote`.`rating`),0) AS points > > FROM `votes` AS `Vote` > > LEFT JOIN `squads` AS `Squad` > > ON (`Vote`.`squad_id` = `Squad`.`id`) > > LEFT JOIN `weeks` AS `Week` > > ON (`Vote`.`week_id` = `Week`.`id`) > > WHERE `Week`.`season_id` = 1 AND `Squad`.`active`=1 AND 1=1 > > GROUP BY `Squad`.`id` > > ORDER BY `points` DESC, `Squad`.`id` ASC; > > > Any pointers? At this point I'm lost, and wondering if I'd be better > > off simply using Model::execute(). --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "CakePHP" 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?hl=en -~----------~----~----~----~------~----~------~--~---
