CakePHP find with MAX

Question

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)

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

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

Comments

[Lawrence Barsanti]

Furthermore, using aggregate functions, like MAX, does produce the desired result for id when you do something like: SELECT id, MAX(created) FROM messages. If you want the id of the message with the latest time stamp, you need to use something like: SELECT id, created FROM messages ORDER BY created DESC LIMIT 1.

[Nightwolf]

Please note that the field * is in all technicality wrong when using group by clause, but that is not relevant to the question.

[Harsha M V]

am stil getting only one result.. Array ( [0] => Array ( [0] => Array ( [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 ) ) )

[Nightwolf]

The sql that should be created is SELECT MAX(Node.created),* FROM messages AS Message, nodes AS Node WHERE Message.node_id = Node.id GROUP BY Message.user_id. Run this query in your db and it should return with the right results. But I believe you have other database relationships that influence the query cakephp creates.

[Nightwolf]

I presume this is the sql dump before you used my suggestion. Could you please give the dump of the sql using my code snippet?

[Harsha M V]

it provided the same thing without the contains

[Nightwolf]

Please run this in your database: SELECT MAX(Node.created),* FROM messages AS Message, nodes AS Node WHERE Message.node_id = Node.id GROUP BY Message.user_id and let me know if the expected results is returned.

[Harsha M V]

i get three results. with the message ids = 1 2 6

[Nightwolf]

Sorry, I forgot about the *. Do you understand what group by does? Only the max value and user_id is what you expect it to be, the rest (*) will be inconsistent. So you need to be specific on what data you would like to get. As example, "you would like to get the corresponding message id for the max created message per user". Hopefully you don't need the full row, but if you do then cakephp won't be able to handle that for you, you will need to create a custom query.

[Harsha M V]

i actually removed the * and just added the id to run the query

[Nightwolf]

The message id pulls the first from the list and not the actual message id that corresponds to the max created when using aggregate functions. That is why to answer this in the optimal way you will need to tell me what value you need to correspond with the max, and if it is the whole row of data it will be a custom query.

[Harsha M V]

what i am trying to do is - build the inbox of messages for a user like in Facebook Messaging. Where when u go to the inbox u will find the latest message from the conversation with the user in reverse chronological order. In the node table i store the details of the message like -> message, author and timestamp. In Message table i store two references to the one row in nodes, one for each user of the conversation.

[Nightwolf]

let us continue this discussion in chat