0

There is two table: user, comments

The structure is like this:

user
-----
id
name

comments
--------
id
title
user_id
comments_id

Notice that the comments_id is the parent ID of that comment record, that means, if comments_id is null, it is a POST, if it is not null, it is a REPLY.

So I would like to select them and ordering like this

Post 1
 Reply 1 for Post 1
 Reply 2 for Post 1
Post2
...

And here is the codeigniter query I have attempt, I also need the user table for getting the user name

$this->db->select('c.id as id, u.name as username, c.txtMsg as txtMsg, c.createDate as createDate, c.updateDate as updateDate, c.imageURL as imageURL, c.postID as postID');
        $this->db->from('comment as c, user as u');
        $this->db->where('u.id = c.userID');
        $this->db->order_by('c.id', $order_type);

How to construct the query to order the list? Thanks for helping.

user782104
  • 13,233
  • 55
  • 172
  • 312
  • 2
    possible duplicate of [Achieve hierarchy, Parent/Child Relationship in an effective and easy way](http://stackoverflow.com/questions/11064913/achieve-hierarchy-parent-child-relationship-in-an-effective-and-easy-way) – vhu Jun 09 '14 at 10:31
  • Thanks for your info, I am going to read it now – user782104 Jun 09 '14 at 10:33
  • it seems to be a common query but it turns out more complex than I expected , is it better I handle it with application logic ? Thanks – user782104 Jun 09 '14 at 10:38
  • If it's just single level of hierarchy query is very straightforward but to handle arbitrary number parent-child relationships you should handle it in the application. – vhu Jun 09 '14 at 11:11

0 Answers0