cakephp , the subquery (2)

时间:2022-08-29 19:22:21

Cakephp 框架帮我们做了很多的工作,的确省了我们很多工作,提高了效率。

但是,碰到一些比较复杂的查询时,还是有些问题,官方的cookbook api 有说明一些详细的用法,但感觉还是不太够,有些地方讲的不够清楚。

*上的这个问题不错

http://*.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 

cakephp , the subquery (2)的更多相关文章

  1. cakephp &comma; the subquery

    Cakephp 框架帮我们做了很多的工作,的确省了我们很多工作,提高了效率. 但是,碰到一些比较复杂的查询时,还是有些问题,官方的cookbook api 有说明一些详细的用法,但感觉还是不太够,有些 ...

  2. &lbrack;数据库&rsqb;cakephp操作ENUM、tinyint等类型的一点说明

    之前无法正常更新ENUM类型的数据,感觉是框架函数实现的bug. 问题很诡异,因为INIT的时候是可以成功写入的,没理由UPDATE的时候不成功. 前后琢磨了一下午,发现了一点蛛丝马迹才终于想通.问题 ...

  3. MySQL----This version of MySQL doesn&&num;39&semi;t yet support &&num;39&semi;LIMIT &amp&semi; IN&sol;ALL&sol;ANY&sol;SOME subquery

    This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'的意思是,这版本的 MySQL 不支持使 ...

  4. Oracle索引失效问题:WHERE C1&equals;&&num;39&semi;&&num;39&semi; OR C2 IN&lpar;SubQuery&rpar;,并发请求时出现大量latch&colon; cache buffers chains等待

    问题描述: 项目反馈某功能响应时间很长,高峰期时系统整体响应很慢... 获取相应的AWR,问题确实比较严重,latch: cache buffers chains等待,因为这些会话SQL执行时间太长, ...

  5. cakephp 打印出SQL语句

    最近一直在使用cakephp这个框架学习,最近发现了一些问题,就是怎样将SQL语句打印出来进行调试,方法如下: $db=ConnectionManager::getDataSource('defaul ...

  6. &lbrack;慢查优化&rsqb;慎用MySQL子查询,尤其是看到DEPENDENT SUBQUERY标记时

    案例梳理时间:2013-9-25 写在前面的话: 在慢查优化1和2里都反复强调过 explain 的重要性,但有时候肉眼看不出 explain 结果如何指导优化,这时候还需要有一些其他基础知识的佐助, ...

  7. This version of MySQL doesn&&num;39&semi;t yet support &&num;39&semi;LIMIT &amp&semi; IN&sol;ALL&sol;ANY&sol;SOME subquery 解决方法

    This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'的意思是,这版本的 MySQL 不支持使 ...

  8. DEPENDENT SUBQUERY” 和 &OpenCurlyDoubleQuote;SUBQUERY”

    http://blog.163.com/li_hx/blog/static/183991413201642410122327/ mysql> CREATE TABLE t1 (a INT, b ...

  9. CakePHP之请求与响应对象

    请求与响应对象 请求与响应对象在 CakePHP 2.0 是新增加的.在之前的版本中,这两个对象是由数组表示的,而相关的方法是分散在RequestHandlerComponent,Router,Dis ...

随机推荐

  1. 号外号外:9月13号《Speed-BI云平台案例实操--十分钟做报表》开讲了

    引言:如何快速分析纷繁复杂的数据?如何快速做出老板满意的报表?如何快速将Speed-BI云平台运用到实际场景中?         本课程将通过各行各业案例背景,将Speed-BI云平台运用到实际场景中 ...

  2. Apache Spark2&period;0正式发布

    Apache Spark2.0正式发布 7月26日起Databricks开始提供Apache Spark 2.0的下载,这个版本是基于社区在过去两年的经验总结而成,不但加入了用户喜爱的功能,也修复了之 ...

  3. 对XXX(数字)安全卫士实在是忍无可忍了,为什么一定要像日本鬼子强奸妇女一样强奸我们这些弱小者

    一直一来对XXX(数字)安全卫士非常痛恨,无耻,恶心,没有底线,还有对待我们这些弱小者,就像当年日本鬼子强奸妇女一样,血粼粼的虐杀我们这些弱小者,无法反抗,又必须接受. 你强制杀掉别人的ADB 就算了 ...

  4. Java面试15&vert;网络

    1.TCP(Transmission Control Protocol)三次握手与四次分手 TCP在不可靠的传输信道上提供了可靠传输的抽象,隐藏了我们的应用程序大部分的复杂性功能:丢包重传,按序传送, ...

  5. sublime 设置字体

    通过菜单Preferences/Settings - User,添加下面这行配置就可以修改字体: "font_face": "Courier New", &qu ...

  6. python函数(6):内置函数和匿名函数

    我们学了这么多关于函数的知识基本都是自己定义自己使用,那么我们之前用的一些函数并不是我们自己定义的比如说print(),len(),type()等等,它们是哪来的呢? 一.内置函数 由python内部 ...

  7. MFC中char&ast;&comma;string和CString之间的转换

    MFC中char*,string和CString之间的转换 一.    将CString类转换成char*(LPSTR)类型 方法一,使用强制转换.例如:  CString theString( &q ...

  8. vs code中文扩展包

    vs code 中文拓展安装失败时,可以手动下载安装,下载对版本的中文包. https://marketplace.visualstudio.com/_apis/public/gallery/publ ...

  9. bash中通过设置PS1变量改变提示符颜色

    参考 <Prompt Magic> ubuntu初始时bash提示符的颜色同程序输出的颜色相同,当大量有输出时,找到输出信息开始的地方往往很费劲.如果把提示符的颜色变成更为醒目的颜色,那么 ...

  10. 简介浏览器内核与JavaScript引擎

    本文介绍了常用浏览器内核与JavaScript引擎 一.浏览器内核 Rending Engine, 顾名思义,称之为渲染网页内容的,将网页的代码转换为你看得见的页面,因为是排版,所以排版,所以肯定会有 ...