CakePHP, the subquery (2)

The Cakephp framework does a lot of work for us, and it does save us a lot of work and improve efficiency.

However, when encountering some more complex queries, there are still some problems, the official cookbook API has some detailed usage, but the feeling is not enough, some places are not clear enough.

That's a good question on 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_namefrom referees as Refereeinner JOIN (    SELECT a.id, a.referee_id from    assumptions a    WHERE a.season_id =7) as assumption on referee.id = Assumption.refere e_id

The answer

Since 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 (                        ' table ' = = ' Assumptions ',                        ' alias ' = ' assumption ',                        ' type '  = ' INNER ',                        ' ForeignKey ' and '    = 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 is 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-seemed like years (several hours) and reading cake source ... if you want to simply type your subquery into Y Our cake conditions ...

It uses PHP stdClass not an array entry ... it would 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 Belon gs_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 the 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 Belon gs_to= ' $category _id ') ";        $options [' conditions '] [] = $subquery;    } else {        //get random items ... no category selected?        $options [' limit '] =;    }    return $this->find (' All ', $options);

　　

that ' s what happens behind the Scen ES, but the proper-do it would is to follow the instructions in the Cookbook. –  brad koch  ; oct 7 ' one at 19:31 if you goto your link and go down t O "sub-queries" you'll see a huge overhead to generate the one line of SQL shown above and this are short and sweet. There is plenty of queries cake "can ' t" do. If your doing multi-nested subqueries Imagine how horrible the cookbook would Be. –  duante oct 7 ' all at 21:39 the only reason this method saves s Pace 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 are to simply use $this->model->query (). Agreed, the guide's method is a long, but it doesn ' t help that the author of the example code didn ' t do the greatest JOB.&NB Sp;–  brad koch oct 7 ' one at 21:55 1 Only difference if you use query () directly need to write the whole query is not just a exception for the sub query. I have updated the example with a example to show this. –duante

CakePHP, the subquery (2)