title: CakePHP Subquery from SQL
body: <pre><code>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 ;
  </code></pre>
  
  <p>I am trying to create a subquery in Cake. but not sure how to start :(</p>
  
  <p>This is the SQL i want to execute </p>
  
  <pre><code>SELECT *
  FROM (
  
  SELECT *
  FROM messages AS msg
  WHERE user_id =1
  ORDER BY modified DESC
  ) AS latest_message
  GROUP BY reciever_id
  </code></pre>
  
  <p>Is it better to use sub Queries or write SQL statement ?</p>