Laravel recursion - finding the "last post"

Question

I have a forums system with many categories which has many threads which has many posts.

So with given forum, I should be able to figure out the last post that specific forum has. A forum can have many subforums (aka children). I have only been able to do part of this.

Table demo:

| id | parent_id | name              | is_category |
|----|-----------|-------------------|-------------|
| 1  | 0         | Suggestions       | 1           |
| 2  | 1         | site suggestions  | 0           |
| 3  | 1         | forum suggestions | 0           |
| 4  | 2         | bugs              | 0           |

Here's my code:

public function lastPost()
{
    foreach ($this->threads()->orderBy('updated_at')->get() as $thread) {
        $post = $thread->lastPost();
    }
    if ($this->hasSubforum()) {
        foreach ($this->subforums as $subforum) {
            $post = $subforum->lastPost();
        }
    }
    return $post;
}

As you can see, lastPost() is called until the forum has no more subforums. I am getting the last post from the latest thread in a subforum. So far so good. However, the last post would be the order the subforum is in. So if the last post was in the second last subforum, it returns the last post of the last subforum, since that last forum was returned last in the recursion.

how can I fix this?

Thanks!

check the date and only set `$post` if the date is more recent (or even the `id` of the post - assuming there is one - assuming it's serial)? — Tyler Sebastian, May 10 '16 at 19:11
You can probably use the last method on your $this->threads()...etc collection, right? https://laravel.com/docs/5.1/collections#method-last — ExohJosh, May 10 '16 at 19:54

score 1 · Answer 1 · edited May 23 '17 at 10:33

I think you should do a different approach to this problem. The way you are trying to do will crash your database when you have a considerable number of users. If you know how deep this tree would be, its better for performance to do a lot of "left join" like this:

select  d3.parent_id as parent3_id,
        d2.parent_id as parent2_id,
        d1.parent_id as parent_id,
        d1.id as product_id,
        d1.name
from      demo d1
left join demo d2 on d2.id = d1.parent_id 
left join demo d3 on d3.id = d2.parent_id 
... join as many as you think it will have data ...
where  $this->id in (d1.parent_id, 
               d2.parent_id, 
               d3.parent_id) 
order by 1, 2, 3;

In this case you'll do just 1 query, in your case you'll do n+1 queries, or even more if using lazy loading.

Another way will be to create a "path" column, like '1/5/19/27/34', that will indicate all parent ids.

You could also create a "last_post" table, that will indicate what was the last post for each category. It will improve your performance also.

This post has a lot of information: How to create a MySQL hierarchical recursive query

Unfortunately I do not know how deep it is going to be – Taylor May 11 '16 at 00:00 — Taylor, May 11 '16 at 00:00

score 0 · Answer 2 · answered May 10 '16 at 21:26

Alter recursive function to accept a post parameter and compare dates.

public function lastPost($post = null)
{
    foreach ($this->threads()->orderBy('updated_at')->get() as $thread) {
        $cur_post = $thread->lastPost();
        if ($post === null) {
            $post = $cur_post;
        }
        else {
            $cur_post_date = new DateTime($cur_post->date_added); // Or whatever you use to get last post date
            $post_date = new DateTime($post->date_added);
            if ($cur_post_date > $post_date) {
                $post = $cur_post;
            }
        }  
    }
    unset($cur_post, $cur_post_date, $post_date);
    if ($this->hasSubforum()) {
        foreach ($this->subforums as $subforum) {
            $post = $subforum->lastPost($post);
        }
    }
    return $post;
}

However as Felippe Duarte said, recursively querying the database is a bad thing so look for alternatives if you're going to use the forum in production.

Laravel recursion - finding the "last post"

2 Answers2