php CakePHP 用 MAX 查找
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8415386/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CakePHP find with MAX
提问by Harsha M V
Tables and dummy data:
表格和虚拟数据：
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.
我想要的是来自另一个用户的特定用户的最新消息。例如：Facebook 收件箱，您可以在其中找到与人的对话以及上次对话和时间顺序。
What I tried:
我试过的：
$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')),
),
),
));
What I got:
我得到了什么：
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
)
)
)
)
)
I am not getting the messages with the id: 4 and 7 returned.
我没有收到带有 id: 4 和 7 的消息。
SQL DUMP
SQL 转储
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)
采纳答案by Nightwolf
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'));
This should give you 3 results, one for each user.
这应该为您提供 3 个结果，每个用户一个。
回答by alieninlondon
Here's a solution under the Example using GROUP BYheadline. What worked for me is in the line of:
这是示例下使用 GROUP BY标题的解决方案。对我有用的是：
$this->Model->find('all', array(
'fields' => 'MAX(Model.wanthighestfield) as wanthighestfield',
'group' => 'wantuniquefield'
));
Results in an array with the highest wanthighestfield for each wantuniquefield. I found that adding fields to the fields options is pointless as CakePHP will not fetch the fields from the same line for the array item.
结果是每个想要的唯一字段具有最高的想要最高字段的数组。我发现向字段选项添加字段毫无意义，因为 CakePHP 不会从数组项的同一行中获取字段。
回答by Oldskool
Try looking at your SQL debug log by putting this in your view:
尝试通过将其放在您的视图中来查看您的 SQL 调试日志：
$this->Element('sql_dump');
Note that this only produces output if your debug level is not zero. I suspect that your MAX query only selects one single node with the highest value and thus doesn't return any other nodes. Ordering by created rather then selecting it with MAX would be my best bet to get this thing going.
请注意，这仅在您的调试级别不为零时才产生输出。我怀疑您的 MAX 查询仅选择具有最高值的单个节点，因此不会返回任何其他节点。通过创建而不是使用 MAX 选择它是我最好的选择，可以让这件事继续下去。
