随笔- 1793 文章- 1 评论- 61 阅读- 298万

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