问题 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');

我想要的是来自其他用户的特定用户的最新消息。例如:Facebook收件箱,您可以在其中找到与人的对话以及最后的对话和时间顺序。

我尝试了什么:

$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的消息。

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)

9981
2017-12-07 12:33


起源

您创建的节点编号4的日期早于节点编号3.这似乎不正确。 - Nightwolf
编号5,6用于节点3,编号7,8用于节点4 - Harsha M V


答案:


如果我正确理解你,这会给你你想要的结果:

$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个结果,每个用户一个。


10
2017-12-07 14:55



请注意,使用group by子句时,字段*在所有技术性方面都是错误的,但这与问题无关。 - Nightwolf
我只获得一个结果..数组([0] =>数组([0] =>数组([创建] => 2011-12-07 12:00:00)[消息] =>数组([id] => 1 [user_id] => 1 [node_id] => 1 [reciever_id] => 15 [created] => 2011-12-07 00:00:00 [已修改] => 2011-12-07 02:00: 00))) - Harsha M V
应该创建的sql是 SELECT MAX(Node.created),* FROM messages AS Message, nodes AS Node WHERE Message.node_id = Node.id GROUP BY Message.user_id。在您的数据库中运行此查询,它应返回正确的结果。但我相信你有其他数据库关系影响cakephp创建的查询。 - Nightwolf
我已经更新了我的问题..并添加了SQL DUMP .. - Harsha M V
在你使用我的建议之前,我认为这是sql转储。你能用我的代码片段给出sql的转储吗? - Nightwolf


尝试通过将其放在视图中来查看SQL调试日志:

$this->Element('sql_dump');

请注意,如果调试级别不为零,则仅生成输出。我怀疑你的MAX查询只选择一个具有最高值的单个节点,因此不会返回任何其他节点。通过创建排序然后用MAX选择它将是我最好的选择。


1
2017-12-07 13:22



此外,使用聚合函数(如MAX)在执行以下操作时会产生所需的id结果:SELECT id,MAX(created)FROM messages。如果您希望消息的id具有最新的时间戳,则需要使用以下内容:SELECT id,created FROM messages ORDER BY created DESC LIMIT 1。 - Lawrence Barsanti
我已更新问题并添加了SQL DUMP - Harsha M V


这是一个解决方案 使用GROUP BY的示例 标题。对我有用的是:

$this->Model->find('all', array(
   'fields' => 'MAX(Model.wanthighestfield) as wanthighestfield',
   'group' => 'wantuniquefield'
));

结果是每个wantuniquefield的具有最高wanthighestfield的数组。我发现在字段选项中添加字段是没有意义的,因为CakePHP不会从数组项的同一行中获取字段。


1
2017-10-17 23:18