• 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 
  • 相关阅读:
    oracle 增加列
    20120621 myeclipse 远程调试
    plsql 参数中in out in的区别讲解
    20120606 随笔
    MYSQL申明变量&显示变量
    arcgis for flex 地图发布服务
    arcserver 地图发布过程
    arcserver 发布地图后浏览器不更新问题
    把一个表的一列插入另一个表的空字段
    mysql运行语句时出现 FUNCTION *** does not exist
  • 原文地址:https://www.cnblogs.com/oxspirt/p/4528658.html
Copyright © 2020-2023  润新知