cakephp , the subquery
Cakephp 框架帮我们做了很多的工作,的确省了我们很多工作,提高了效率。
但是,碰到一些比较复杂的查询时,还是有些问题,官方的cookbook api 有说明一些详细的用法,但感觉还是不太够,有些地方讲的不够清楚。
StackOverflow上的这个问题不错
http://stackoverflow.com/questions/5770032/cakephp-and-subquery
The Question:
How can I write SQL subquery using cake syntax. I know how to code simple query, but I can't handle subquery.
This is the original query:
SELECT Assumption.id, Referee.id, Referee.first_name, Referee.second_name FROM referees AS Referee INNER JOIN ( SELECT a.id, a.referee_id FROM assumptions a WHERE a.season_id =7 ) AS Assumption ON Referee.id = Assumption.referee_id
The answer
Since you didn't understand the syntax, this is the actual query:
$records = $this->Referee->find('all', array( 'fields' => array( 'Assumption.id', 'Referee.id', 'Referee.first_name', 'Referee.second_name' ), 'joins' => array( array( 'table' => 'assumptions', 'alias' => 'Assumption', 'type' => 'INNER', 'foreignKey' => false, 'conditions' => array('Referee.id = Assumption.referee_id', 'Assumption.season_id = 7'), ), ), ) );
Which produces this query:
SELECT `Assumption`.`id`, `Referee`.`id`, `Referee`.`first_name`, `Referee`.`second_name` FROM `referees` AS `Referee` INNER JOIN assumptions AS `Assumption` ON (`Referee`.`id` = `Assumption`.`referee_id` AND `Assumption`.`season_id` = 7)
Which provide the results you are looking for.
Sample output:
Array ( [0] => Array ( [Assumption] => Array ( [id] => 1 [0] => Array ( [id] => 1 [season_id] => 7 [referee_id] => 1 [name] => SomeAssumpton ) ) [Referee] => Array ( [id] => 1 [first_name] => Ref [second_name] => one ) ) )
a