CakePHP and subquery

开发者_运维问答How can I write a SQL subquery using cake syntax? I know how to code a simple query, but I can't handle subqueries.

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

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
                )

        )

)

cdburgess's answer is correct for this situation; it solves the problem in the simplest and most straightforward way.

That being said, when solving the generic question of performing a subquery, the Cookbook documents the preferred solution to the subquery problem. It's not very elegant, but it's the way the guide says to do it.

Or you can simply be very careful and just do a $this->Model->query().

so after what seemed like years (several hours) and reading cake source... if you want to simply type your subquery into your cake conditions...

it uses php stdClass not an array entry... it will just dump your "value" into the query...

    $subquery = new stdClass();
    $subquery->type = "expression";
    $subquery->value = "Product.id IN (select product_id from product_categories where category_id='$category_id' or belongs_to='$category_id')";

    $options['conditions'][] = $subquery;  <- dump the class into your conditions array!

do normal query $this->table->find('all', $options)

EXAMPLE: (normal cake with subquery quickfix)

    //only from my vendor
    $options['conditions']['collection_id'] = $vendor_id;

    //inner join to CollectionProduct
    $options['joins'][0] = array(
        "table" => "collection_products",
        "alias" => "CollectionProduct",
        "type"  => "INNER",
        "conditions" => array(
            "Product.id = CollectionProduct.product_id",
        ),
    );

    //show only from current category
    if ($category_id) {
        $subquery = new stdClass();
        $subquery->type = "expression";
        $subquery->value = "Product.id IN (select product_id from product_categories where category_id='$category_id' or belongs_to='$category_id')";
        $options['conditions'][] = $subquery;
    } else {
        //get 18 random items... no category selected?
        $options['limit'] = 18;
    }

    return $this->find('all', $options);