sql - CakePHP 和子查询

Question

如何使用 cake 语法编写 SQL 子查询？
我知道如何编写一个简单的查询，但我无法处理子查询。

这是原始查询:

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

Answer 1

由于您不了解语法，这是实际查询:

$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'),
                        ),
                ),
            )
        );

产生这个查询:

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)

这提供了您正在寻找的结果。

示例输出:

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
                )

        )

)