CakePHP Subquery from SQL(来自 SQL 的 CakePHP 子查询)
问题描述
CREATE TABLE IF NOT EXISTS `messages` (
`id` int(11) unsigned NOT NULL auto_increment,
`user_id` int(11) unsigned NOT NULL,
`node_id` int(11) unsigned NOT NULL,
`reciever_id` int(11) unsigned NOT NULL,
`created` datetime default NULL,
`modified` datetime default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;
INSERT INTO `messages` (`id`, `user_id`, `node_id`, `reciever_id`, `created`, `modified`) VALUES
(1, 1, 1, 15, '2011-12-07 00:00:00', '2011-12-07 02:00:00'),
(2, 15, 1, 1, '2011-12-07 02:00:00', '2011-12-07 02:00:00'),
(3, 15, 2, 1, '2011-12-07 11:00:00', '2011-12-07 11:00:00'),
(4, 1, 2, 15, '2011-12-07 11:00:00', '2011-12-07 11:00:00'),
(5, 1, 3, 18, '2011-12-07 11:00:00', '2011-12-07 11:00:00'),
(6, 18, 3, 1, '2011-12-07 11:00:00', '2011-12-07 11:00:00'),
(7, 1, 4, 18, '2011-12-07 12:00:00', '2011-12-07 12:00:00'),
(8, 18, 4, 1, '2011-12-07 12:00:00', '2011-12-07 12:00:00');
CREATE TABLE IF NOT EXISTS `nodes` (
`id` int(11) unsigned NOT NULL auto_increment,
`message` text NOT NULL,
`author_id` int(11) unsigned NOT NULL,
`read` tinyint(1) default NULL,
`created` datetime default NULL,
`modified` datetime default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
INSERT INTO `nodes` (`id`, `message`, `author_id`, `read`, `created`, `modified`) VALUES
(1, 'Hi! How are you ? dude wanna meet up this weekend ?', 1, 0, '2011-12-07 02:00:00', '2011-12-07 02:00:00'),
(2, 'Sure. wanna go to Mangalore Pearl to eat Neer Dosa..', 15, 0, '2011-12-07 11:00:00', '2011-12-07 11:00:00'),
(3, 'Hi How are u Buddy ? Long time no see...', 1, 0, '2011-12-07 11:00:00', '2011-12-07 11:00:00'),
(4, 'yeah. are you back in town ? i think we should meet up man. its been ages ....', 18, 0, '2011-12-07 12:00:00', '2011-12-07 12:00:00');
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) unsigned NOT NULL auto_increment,
`first_name` varchar(255) default NULL,
`last_name` varchar(255) default NULL,
`email` varchar(255) default NULL,
`password` varchar(40) default NULL,
`username` varchar(255) default NULL,
`birthday` date default NULL,
`gender` varchar(255) default NULL,
`city_id` int(11) unsigned NOT NULL,
`status` varchar(255) NOT NULL,
`created` datetime default NULL,
`modified` datetime default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;
我正在尝试在 Cake 中创建一个子查询.但不知道如何开始:(
I am trying to create a subquery in Cake. but not sure how to start :(
这是我要执行的 SQL
This is the SQL i want to execute
SELECT *
FROM (
SELECT *
FROM messages AS msg
WHERE user_id =1
ORDER BY modified DESC
) AS latest_message
GROUP BY reciever_id
使用子查询好还是写SQL语句好?
Is it better to use sub Queries or write SQL statement ?
推荐答案
既然您试图按接收者分组,那么为什么不更改查询以检索接收者,然后检索属于每个接收者的消息呢?下面,我假设使用了可包含的行为.
Since you are trying to group by the receiver, then why not alter the query to retrieve the receivers and then the messages that belong to each one? Below, I am assuming use of the containable behavior.
$this->Receiver->find('all', array(
'contain' => array(
'Message' => array(
'conditions' => array('Message.user_id' => 1),
'order' => array('Message.modified' => 'DESC'),
)
)
));
编辑
根据您的评论,我添加了此查询以查看是否有帮助.
I added this query to see if it helps based on your comment.
$this->Message->find(
'all',
array(
'conditions' => array('Message.user_id' => 1),
'fields' => array('Message.*', 'MAX(Message.modified) as max_mod'),
'group' => 'Message.receiver_id'
)
);
这篇关于来自 SQL 的 CakePHP 子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:来自 SQL 的 CakePHP 子查询
基础教程推荐
- 无法在 ubuntu 中启动 mysql 服务器 2021-01-01
- SQL Server 中单行 MERGE/upsert 的语法 2021-01-01
- 将数据从 MS SQL 迁移到 PostgreSQL? 2022-01-01
- 使用pyodbc“不安全"的Python多处理和数据库访问? 2022-01-01
- SQL Server:只有 GROUP BY 中的最后一个条目 2021-01-01
- 如何在 SQL Server 的嵌套过程中处理事务? 2021-01-01
- SQL Server 2016更改对象所有者 2022-01-01
- ERROR 2006 (HY000): MySQL 服务器已经消失 2021-01-01
- 在 VB.NET 中更新 SQL Server DateTime 列 2021-01-01
- Sql Server 字符串到日期的转换 2021-01-01