Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

thebigk's avatar
Level 13

How do I avoid duplicate query while using relationship on self model?

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.

0 likes
7 replies
MichalOravec's avatar

You already aviod to the N+1 problem. You see that select from users table twice because posts and replies have the same users. That's it. For each relationship is one query, post -> users and for posts -> replies -> users

Be carefully with using orWhere.

thebigk's avatar
Level 13

Nope, my question is not about avoiding N+1 problem. My question is about avoiding a duplicate query

select * from userswhereusers.id in (1, 2, 3, 4, 5) that runs every time. I see two entries of that in Laravel Debugbar.

I've tried adding protected $with = ['user'] to my Post model; but it isn't helping.

MichalOravec's avatar

It's not duplicate query. Posts have users and replies have users. Eloquent has to run two different queries for that.

You have exactly the same query because you have posts and replies which belongs to the same users.

Add another reply with different user which doesn't have any post and you will see the difference.

thebigk's avatar
Level 13

Add another reply with different user which doesn't have any post and you will see the difference.

Didn't get you. I only have 1 posts model; which I'm using to render the entire thread.

MichalOravec's avatar
Level 75

So add another post which will be a reply (in this case) with an user which haven't created a post yet (parent in this case).

How do I avoid duplicate query while using relationship on self model?

You can't.

1 like
thebigk's avatar
Level 13

Meh, okay! GOT IT! :-| I wasted 2 hours on it for no reason. THANKS!

Please or to participate in this conversation.