CakePHP Subquery from SQL

Question

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 11:00:00', '2011-12-07 11:00:00'),
(6, 18, 3, 1, '2011-12-07 11:00:00', '2011-12-07 11:00:00'),
(7, 1, 4, 18, '2011-12-07 12:00:00', '2011-12-07 12:00:00'),
(8, 18, 4, 1, '2011-12-07 12:00:00', '2011-12-07 12: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 11:00:00', '2011-12-07 11:00:00'),
(4, 'yeah. are you back in town ? i think we should meet up man. its been ages ....', 18, 0, '2011-12-07 12:00:00', '2011-12-07 12:00:00');


CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `first_name` varchar(255) default NULL,
  `last_name` varchar(255) default NULL,
  `email` varchar(255) default NULL,
  `password` varchar(40) default NULL,
  `username` varchar(255) default NULL,
  `birthday` date default NULL,
  `gender` varchar(255) default NULL,
  `city_id` int(11) unsigned NOT NULL,
  `status` varchar(255) NOT NULL,
  `created` datetime default NULL,
  `modified` datetime default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;

I am trying to create a subquery in Cake. but not sure how to start :(

This is the SQL i want to execute

SELECT *
FROM (

SELECT *
FROM messages AS msg
WHERE user_id =1
ORDER BY modified DESC
) AS latest_message
GROUP BY reciever_id

Is it better to use sub Queries or write SQL statement ?

If you could list details of your tables and a few rows of the data you want back, it will help people give good answers. — Dave, Dec 09 '11 at 14:20

score 1 · Answer 1 · answered Dec 09 '11 at 13:40

1

I'm not really sure what the necessity for a subquery is here, wouldn't something like this do the trick?

$this->Message->find('all', array(
    'conditions' => array('Message.user_id' => 1),
    'order' => array('Message.modified' => 'DESC'),
    'group' => array('Message.receiver_id')
));

This would retrieve all the messages from the user with id 1, ordered by date modified and grouped by receiver_id.

answered Dec 09 '11 at 13:40

Oldskool

34,211
7
53
66

i tried that. First it Groups and then does the Ordering. It doesn't fetch the intended result. – Harsha M V Dec 09 '11 at 13:46

Scott Harwell · Accepted Answer · 2012-01-05T17:33:54.117

0

Since you are trying to group by the receiver, then why not alter the query to retrieve the receivers and then the messages that belong to each one? Below, I am assuming use of the containable behavior.

$this->Receiver->find('all', array(
    'contain' => array(
        'Message' => array(
            'conditions' => array('Message.user_id' => 1),
            'order' => array('Message.modified' => 'DESC'),
        )
    )
));

EDIT

I added this query to see if it helps based on your comment.

$this->Message->find(
    'all',
    array(
        'conditions' => array('Message.user_id' => 1),
        'fields' => array('Message.*', 'MAX(Message.modified) as max_mod'),
        'group' => 'Message.receiver_id'
    )
);

edited Jan 05 '12 at 17:33

answered Jan 04 '12 at 21:42

Scott Harwell

7,457
2
28
41

i want the latest message from/to every user i am conversing with – Harsha M V Jan 05 '12 at 03:39
i am getting two records. but those records are the first message from the thread. i want the last message in terms of modified. – Harsha M V Jan 05 '12 at 15:48
Are you expecting two records, or more? Try MIN instead of MAX, though MAX should provide you with the most current date. – Scott Harwell Jan 05 '12 at 15:51
i am expecting 2 itself. still the same you can have a look here http://bakasura.in/bmobile/users/messages/ the tables and rows http://pastebin.com/TbxsdGx9 i am expecting 4 and 7 – Harsha M V Jan 05 '12 at 16:05
Can you swap the query back to using max so I can see the results? – Scott Harwell Jan 05 '12 at 16:15
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/6431/discussion-between-scott-harwell-and-harsha-m-v) – Scott Harwell Jan 05 '12 at 16:19
In your debug output on the link you provided you seem to be sorting DESC on the created field - the provided query does a DESC sort on the modified field. That might be a problem right there. – Abba Bryant Jan 06 '12 at 17:55
We made that change in the chat link. He still can't get it to work and I haven't had time to look at it more thoroughly. I think the problem now is that the calculated field isn't part of the table schema so Cake removes and ignores it. I'm pretty sure that this can be done within Cake's functionality. – Scott Harwell Jan 06 '12 at 18:30
:) looking forward for some kinda break through. I am hoping not to use query() – Harsha M V Jan 07 '12 at 05:30

CakePHP Subquery from SQL

2 Answers2

Linked