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