CakePHP Sub Querys, How To?

0
votes

I have a post model (mysql table), and the structure is something like:

id, parent, user_id, title, desc, created, modified

A single thread of conversation consists of a post, followed by all subsequent posts where post.parent matches the original post_id.

Sample Conversation between user_32 and user_40:

110, 0, 32, thread_title, sample_description_here, created_time, modified_time
121, 110, 40, comment_title, sample_comment_here, created_time, modified_time
130, 110, 32, comment_title, sample_comment_here, created_time, modified_time
140, 110, 32, comment_title, sample_comment_here, created_time, modified_time
166, 110, 40, comment_title, sample_comment_here, created_time, modified_time
290, 110, 32, comment_title, sample_comment_here, created_time, modified_time

With plain PHP I simply do the outer query, followed by the inner query and then echo the conversation to the screen, but how do you achieve this with CakePHP??

QUESTION:

How do I construct the query in CakePHP, to display a single conversation composed of (see above) post_id_110, followed by all subsequent posts where post.parent == 110, ordered by created_time DESC.??

2
mysqlcakephpfind

2 Answers

1
votes

In Post model:

var $hasMany = array(
  'Children'=>array(
    'className' => 'Post',
    'foreignKey' => 'parent_id'
   )
 );

(and change 'parent' to 'parent_id' in DB, just a convention). Then in posts controller: 

$this->Post->find('first',array(
   'conditions'=>array(...),
   'contain'=>array('Children')
));

oh yeah, and use Containable. You don't really need that for the purpose, but it makes the find() clearer and definitely helpful later on.

1
votes

in post model:

var $belongsTo = array(
    'ParentPost' => array(
        'className' => 'Post',
        'foreignKey' => 'parent_id',
    ),
)

and then use as every other associate model

btw - parent_id is more cakeish than parent and works with scaffolds