CakePHP和子查询 [英] CakePHP and subquery

问题描述

如何使用蛋白语法编写SQL子查询？
我知道如何编写一个简单的查询，但是我不能处理子查询。

How can I write a SQL subquery using cake syntax? I know how to code a simple query, but I can't handle subqueries.

这是原始查询：

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

推荐答案

由于你不理解语法，这是实际的查询： / p>

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

这会产生以下查询：

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
                )

        )

)

这篇关于CakePHP和子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！