[eluser]codex[/eluser]
If you've ever made a forum you might know the answer.
I've got a 'forum_posts' table which is setup like this:
post_id
post_cat_id
post_parent_id = in case it's a reply the parent is the post_id, else it's 0
post_subject
post_date
I need to get the last post date as well. If the post has replies, the last date is the last replydate, else it's the original post_date. But since I'm selecting on post_parent_id = 0 it's impossible to get replydates. Can it be done with a smart query (subselect or having etc) or do you have to have a seperate query for this to work?
My model
Code:
function get_forum_posts($category_id)
{
$this->db->select('*');
$this->db->from('forum_posts');
$this->db->where('post_cat_id', $category_id);
$this->db->where('post_parent_id', 0);
$this->db->groupby('post_id');
$this->db->orderby('post_date', 'desc');
$query = $this->db->get();
if ($query->num_rows() > 0) {
return $query->result();
}
else {
return FALSE;
}
}