I'm trying to create 1-level nested comment system. That is, each comment can have one reply. My db structure is like this -
id | domain_id | user_id | channel_id | parent_id | title | body
The parent_id is meant to reference the id column on the same table; or it can be NULL.
My model is as follows:
class Post extends Model
{
public function user()
{
$this->belongsTo(User::class);
}
// In the following relationship, I'm using parent_id as the foreign key on the same model.
public function replies()
{
$this->hasMany(Post::class, 'parent_id')->whereNotNull('parent_id');
}
public function channel()
{
$this->belongsTo(Channel::class);
}
}
My PostsController looks like this -
class PostsController extends Controller
{
public function show(Request $request, $id)
{
return Post::where('domain_id', $request->domain_id)->with(['user', 'replies.user', 'channel'])->orWhere('parent_id, $id)->get();
}
}
Laravel Debugbar shows the following query running twice in the PostsController -
select * from userswhereusers.id in (1, 2, 3, 4, 5)
I think this comes from the replies.user relationship that I'm loading. However, it's important to avoid the N+1 problem that I'd run into otherwise.
Is there any way to avoid it? Would really appreciate help.