laravel - How to get latestReply in a Channel without an expensive call?

Question

A Channel has Threads which has a latestReply. I'm trying to show the most recent reply next to the channel.

I have something that works but is a 620ms call...

Here is my current code:

public function latestReply()
{
    return $this->hasOneThrough(Reply::class, Thread::class, 'channel_id', 'thread_id', 'id', 'id')->latest();
}

Then I iterate through the Channels and call $channel->latestReply->created_at. This is definitely not optimal for me as it significantly slows down the load time.

Can anyone please let me know of a better way to get the latest Reply within a Channel? I think I have an idea and will be trying to figure it out in the meantime.

Thanks!

question from:https://stackoverflow.com/questions/65879558/how-to-get-latestreply-in-a-channel-without-an-expensive-call

Answer 1

How about you add a new hasOne with your own implementation with joins rather than the default queries executed by laravel

class Channel extends Model
{
    //.. other stuff
    // Approach 1 with joins
    public function latestReply()
    {
        return $this->hasOne(Thread::class, 'channel_id')
                    ->join('replies as a', 'threads.id', '=', 'a.thread_id')
                    ->leftJoin('replies as a1', function ($join) {
                        $join->on('a.thread_id', '=', 'a1.thread_id')
                             ->whereRaw(DB::raw('a.created_at < a1.created_at'));
                       })
                    ->whereNull('a1.thread_id')
                    ->select('a.*', 'channel_id');
    }
    // Approach 2 with whereExists
    public function reply()
    {
        return $this->hasOne(Thread::class, 'channel_id')
                    ->join('replies as a', 'threads.id', '=', 'a.thread_id')
                    ->whereExists(function ($query) {
                       $query->select(DB::raw(1))
                             ->from('replies as b')
                             ->whereRaw(DB::raw('a.thread_id = b.thread_id'))
                             ->havingRaw('max(b.created_at) = a.created_at')
                             ;
                   })->select('a.*', 'channel_id');
    }
}

And then use eager loading for latest reply

$channels = Channel::with(['latestReply','recentReply'])->get();

Also a composite index on replies (thread_id,created_at) will be good to have to improve performance of above query

DEMO