CakePHP 从子查询中选择 (SELECT foo from SELECT(...))
我有一个使用子查询的 CakePHP 查询。虽然我找到了向我展示如何在 Cake 中使用子查询作为条件的文档,但我一直无法找到使用子查询作为表的方法。
换句话说,我试图表达这样的东西:
SELECT `Status`.`name`,
COUNT(*) as total_count,
COUNT(NULLIF(over_one_year, 0)) as over,
COUNT(NULLIF(over_one_year, 1)) as under
FROM (
SELECT ((YEAR('##some date##') - YEAR(COALESCE(start_date, '1900-01-01'))) -
(RIGHT(DATE('##some date##'), 5) < RIGHT(COALESCE(start_date, '1900-01-01'), 5))
>= 1) as over_one_year,
status_id FROM `users` WHERE `user_id` IN (##some list of ids##)) as User
LEFT JOIN `statuses` AS `Status` ON (`User`.`status_id` = `Status`.`id`)
GROUP BY id;
在蛋糕中。
还有希望吗?
I have a CakePHP query that uses subqueries. While I've found documentation that shows me how to use subqueries in Cake for conditions, I haven't been able to find a way to use the subquery as the table.
In other words, I'm trying to express something like this:
SELECT `Status`.`name`,
COUNT(*) as total_count,
COUNT(NULLIF(over_one_year, 0)) as over,
COUNT(NULLIF(over_one_year, 1)) as under
FROM (
SELECT ((YEAR('##some date##') - YEAR(COALESCE(start_date, '1900-01-01'))) -
(RIGHT(DATE('##some date##'), 5) < RIGHT(COALESCE(start_date, '1900-01-01'), 5))
>= 1) as over_one_year,
status_id FROM `users` WHERE `user_id` IN (##some list of ids##)) as User
LEFT JOIN `statuses` AS `Status` ON (`User`.`status_id` = `Status`.`id`)
GROUP BY id;
in cake.
Is there any hope?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
我认为这超出了 cakephp 的 ORM 能力,或者不值得花时间去了解它如何使用它。
您甚至可以使用 $this->Model->query() 方法 (http:// book.cakephp.org/view/1027/query)或者至少它仍然是php,只需编写自己的类或函数来进行特殊查询。
I think this is beyond cakephp's ORM capabilities, or it is not worth to spend the time to find out how it could work with it.
You can even use the $this->Model->query() method (http://book.cakephp.org/view/1027/query ) or at least it is still php, just write your own classes or functions to make special queries.