Needing nested query results with one call

[eluser]Kyle Ellman[/eluser]
Hello,

I'm trying to find the fasted way to get a set of results from a database, but I feel that my solution could be a lot better. Here's some information about the problem:

I have a table of comments with the following schema:

Code:

commentID, parent, profile, userID, comment, timestamp

CommentID is a unique ID.
Parent is the comment's parent (I'm allowing replying of comments up to 1 level deep). If the comment is an original, the parent is set to 0.
Profile is the profile on which the comment was posted.
UserID is the user who posted the comment.
Comment is the comment itself.
Timestamp is the time the comment was posted.

I'm wanting to grab a limited number of original comments, in order of new to old, and their replies, in oder of old to new, in a list of results as follows:

Code:

comment 3
comment 3 reply 1
comment 3 reply 2
comment 2
comment 2 reply 1
comment 1
comment 1 reply 1
comment 1 reply 2
comment 1 reply 3

But I also need to be able to control limit and offset (but only on the original comments).

My code currently looks like this, with one call to get the original comments, but another call to get each original comment's replies:

Code:

function profile_comments($profile, $limit = NULL, $offset = NULL)
{
  $this->db->order_by('timestamp', 'DESC');
  $this->db->where(array('profile' => $profile, 'parent' => 0));
  $query = $this->db->get('comments', $limit, $offset);
  
  $parents = $query->result_array();
  $comments = array();
  
  foreach($parents as $key => $comment)
  {
   array_push($comments, $comment);
  
   $this->db->order_by('timestamp', 'ASC');
   $this->db->where(array('profile' => $profile, 'parent' => $comment['commentID']));
   $comments = array_merge($comments, $this->db->get('comments')->result_array());
  }
  
  return $comments;
}

I would like to be able to to do all of this with one database call. Any ideas?

Thanks.