MySQL: Get last forum post date

[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;
        }
    }

[eluser]mironcho[/eluser]
One approach is to use such sub select (sorry - query is not translated into CI AR)

Code:

SELECT
    f.*,
    (SELECT post_date FROM forum_posts WHERE post_id = f.post_id OR post_parent_id = f.post_id ORDER BY post_date DESC LIMIT 1) AS last_post_date
FROM
    forum_posts AS f
WHERE
    post_parent_id = '0'
    AND post_cat_id = $category_id
ORDER BY
    last_post_date DESC

Other, faster, solution is to use additional field in your table - last_post_date. Just update it in "master post" whenever there is a change in it's thread.

Hope this helps.
Miro

[eluser]codex[/eluser]
Actually, the second approach is not bad at all. Now why didn't I think of that

Thanks!

[quote author="mironcho" date="1201667182"]One approach is to use such sub select (sorry - query is not translated to CI AR)

Code:

SELECT
    f.*,
    (SELECT post_date FROM forum_posts WHERE post_id = f.post_id OR post_parent_id = f.post_id ORDER BY post_date DESC LIMIT 1) AS last_post_date
FROM
    forum_posts AS f
WHERE
    post_parent_id = '0'
    AND post_cat_id = $category_id
ORDER BY
    last_post_date DESC

Other, faster, solution is to use additional field in your table - last_post_date. Just update it in "master post" whenever there is a change in it's thread.

Hope this helps.
Miro[/quote]

[eluser]elvix[/eluser]
I have a copy of BBpress around for precisely this purpose.

BBpress is a pretty lightweight forum (making it easy to find examples you're looking for), and comes from the Wordpress crew.

BBpress uses last_post_date, if I remember correctly.