CakePHP find with MAX

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.

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.

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)

回答1:

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.

回答2:

Try looking at your SQL debug log by putting this in your view:

$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.

回答3:

Here's a solution under the Example using GROUP BY headline. What worked for me is in the line of:

$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.