The Cakephp framework does a lot of work for us, and it does save us a lot of work and improve efficiency.
However, when encountering some more complex queries, there are still some problems, the official cookbook API has some detailed usage, but the feeling is not enough, some places are not clear enough.
That's a good question on 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_namefrom referees as Refereeinner JOIN ( SELECT a.id, a.referee_id from assumptions a WHERE a.season_id =7) as assumption on referee.id = Assumption.refere e_id
The answer
Since 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 ( ' table ' = = ' Assumptions ', ' alias ' = ' assumption ', ' type ' = ' INNER ', ' ForeignKey ' and ' = 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 is 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
CakePHP, the subquery