• cakephp , the subquery (2)


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

      Another Answer

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

      

    1  
    That's what happens behind the scenes, but the proper way to do it would be to follow the instructions in the Cookbook. –  Brad Koch Oct 7 '11 at 19:31
    1  
    if you goto your link and go down to "Sub-queries" you will see a huge overhead to generate the one line of sql shown above and this is short and sweet. there are plenty of queries cake "can't" do. If your doing multi-nested subqueries imagine how horrible the cookbook would be. –  duante Oct 7 '11 at 21:39
    1  
    The only reason this method saves space is because the subquery was written explicitly. The OP specifically stated they wanted to use cake methodology. The shortest route if you don't care is to simply use $this->Model->query(). Agreed, the guide's method is long, but it doesn't help that the author of the example code didn't do the greatest job. –  Brad Koch Oct 7 '11 at 21:55
    1  
    Only difference if you use query() directly you need to write the whole query not just an exception for the sub query. I have updated the example with an example to show this. –  duante 
  • 相关阅读:
    JavaScript中Null和Undefined的区别
    javascript中的计算题
    数组去重
    javascript面向对象中继承实现的几种方式
    数列求值 题解
    首字母变大写 题解
    发工资咯:) 题解
    绝对值排序 题解
    数列有序 题解
    母牛的故事 题解
  • 原文地址:https://www.cnblogs.com/oxspirt/p/4528658.html
Copyright © 2020-2023  润新知