CakePHP发现与MAX [英] CakePHP find with MAX
本文介绍了CakePHP发现与MAX的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
表格和虚拟资料:
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 12:00:00', '2011-12-07 12:00:00'),
(6, 18, 3, 1, '2011-12-07 12:00:00', '2011-12-07 12:00:00'),
(7, 1, 4, 18, '2011-12-07 07:00:00', '2011-12-07 07:00:00'),
(8, 18, 4, 1, '2011-12-07 07:00:00', '2011-12-07 07: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 12:00:00', '2011-12-07 12:00:00'),
(4, 'yeah. are you back in town ? i think we should meet up man. its been ages ....', 18, 0, '2011-12-07 07:00:00', '2011-12-07 07:00:00');
我想要的是另一个用户的特定用户的最新消息。
What I want is the latest message for a particular user from another user. For example: Facebook Inbox, where you find conversations with people and the last conversation and time in the order of time.
我试过的:
$messages = $this->User->Message->find('all', array('conditions' => array('user_id' =>
$user_id), 'group by' => 'Message.reciever_id', 'order' =>
'Message.created DESC', 'fields' => array('MAX(Message.created)', '*'),
'contain' => array('Node' => array('fields' => array('id', 'message',
'author_id', 'read', 'created')), 'Reciever' => array('fields' => array('id',
'first_name', 'last_name'), 'Oauth' => array('fields' => array('provider_uid'))))));
我得到了什么:
Array (
[0] => Array
(
[0] => Array
(
[MAX(`Message`.`created`)] => 2011-12-07 12:00:00
)
[Message] => Array
(
[id] => 1
[user_id] => 1
[node_id] => 1
[reciever_id] => 15
[created] => 2011-12-07 00:00:00
[modified] => 2011-12-07 02:00:00
)
[Node] => Array
(
[id] => 1
[message] => Hi! How are you ? dude wanna meet up this weekend ?
[author_id] => 1
[read] => 0
[created] => 2011-12-07 02:00:00
)
[Reciever] => Array
(
[id] => 15
[first_name] => Mayur
[last_name] => Polepalli
[Oauth] => Array
(
[0] => Array
(
[provider_uid] => 551131489
[id] => 15
[user_id] => 15
)
)
)
)
)
我没有收到ID为4和7的邮件。
I am not getting the messages with the id: 4 and 7 returned.
SQL DUMP
SELECT MAX(`Message`.`created`), `Message`.*, `Node`.`id`, `Node`.`message`, `Node`.`author_id`, `Node`.`read`, `Node`.`created`, `Reciever`.`id`, `Reciever`.`first_name`, `Reciever`.`last_name` FROM `messages` AS `Message` LEFT JOIN `nodes` AS `Node` ON (`Message`.`node_id` = `Node`.`id`) LEFT JOIN `users` AS `Reciever` ON (`Message`.`reciever_id` = `Reciever`.`id`) WHERE `user_id` = 1 ORDER BY `Message`.`created` DESC
SELECT `Node`.`id`, `Node`.`message`, `Node`.`author_id`, `Node`.`read`, `Node`.`created` FROM `nodes` AS `Node` WHERE `Node`.`id` = 1
SELECT `Reciever`.`id`, `Reciever`.`first_name`, `Reciever`.`last_name` FROM `users` AS `Reciever` WHERE `Reciever`.`id` = 15
SELECT `Oauth`.`provider_uid`, `Oauth`.`id`, `Oauth`.`user_id` FROM `oauths` AS `Oauth` WHERE `Oauth`.`user_id` = (15)
推荐答案
正确地,这将给您所期望的结果:
If I am understanding you correctly this would give you your desired result:
$messages = $this->User->Message->find('all', array(
'conditions' => array('user_id' => $user_id),
'fields' => array('MAX(Node.created) AS created', '*'),
'group by' => 'Message.user_id',
'order' => 'reciever_id'));
这应该给你3个结果,每个用户一个。
This should give you 3 results, one for each user.
这篇关于CakePHP发现与MAX的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文