Biscotte
3 months ago

Nested subqueries

Posted 3 months ago by Biscotte

Hi,

So I have a basic forum structure with categories, sub categories, posts and replies.

I want to retrieve all categories with their sub categories, and for each sub category, I also need its latest post, and for each latest post, its latest reply.

So I did 2 sub queries like this in my SubCategory model and my Post model respectively:

SubCategory:

   public function latestPost()
    {
        return $this->belongsTo('App\Post');
    }

    public function scopeWithLatestPost($query)
    {
        $query->addSelect(['latest_post_id' => Post::select('id')
            ->whereColumn('sub_category_id', 'sub_categories.id')
            ->latest()
            ->take(1)
        ])->with('latestPost');
    }

Post:

    public function latestReply()
    {
        return $this->belongsTo('App\Reply');
    }

    public function scopeWithLatestReply($query)
    {
        $query->addSelect(['latest_reply_id' => Reply::select('id')
            ->whereColumn('post_id', 'posts.id')
            ->latest()
            ->take(1)
        ])->with('latestReply');
    }

They both work well. Now this is how I retrieve all of it:

Category::with(['subCategories' => function ($query) {
                    $query->withLatestPost()->get();
                }])->get();

Except I don't get the last reply for each post. Would you know how to call the withLatestReply() method?

Thanks

Please sign in or create an account to participate in this conversation.