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
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…