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