How to use wherePivot() inside whereHas() in a query ?

Hello,

I have to filter some data via a specific pivot table column.

$events = Event::query()
    ->whereHas('teams', function ($query) use ($user) {
		$query->whereHas('users', function ($query) {
			$query->wherePivot() // here the pivot table is team_user and I need to check a column from the pivot table		
});
	})
    ->orWhereDoesntHave('teams')
    ->get();

It doesn't work, I think that wherePivot is only for the declaration of a relationship. Is it possible to use it inside a query ? Or perhaps an equivalent function for a query ?

Furthermore the condition is that the created_at date from the pivot table has to be inferior than the creation date of the event.

Thanks for your help.

tisuchi

2 years ago

Level 70

@vincent15000 Does it help?

$events = Event::query()
    ->whereHas('teams', function ($query) use ($user) {
        $query->whereHas('users', function ($query) use ($user) {
            $query->wherePivot('created_at', '<', $user->created_at);
        });
    })
    ->orWhereDoesntHave('teams')
    ->get();

3 likes

vincent15000

2 years ago

Level 63

@tisuchi Thank you for you help.

I have already thought about this code for which I get an error saying that the pivot field doesn't exist in the table.

Perhaps because it's Laravel 7.x ?

Furthermore I would need to replace $user->created_at by $event->created_at and this is a problem because I don't see how it's possible to do that.

2 likes

tisuchi

2 years ago

Best Answer

Level 70

@vincent15000 I see. In that case, how about this?

$events = Event::query()
    ->whereHas('teams.users', function ($query) use ($user) {
        $query->where('team_user.created_at', '<', DB::raw('events.created_at'));
    })
    ->orWhereDoesntHave('teams')
    ->get();

4 likes

vincent15000

2 years ago

Level 63

@tisuchi That's pretty interesting, I never used DB::raw() inside a where() close like this. It should work, I will try this tomorrow and I tell you if it's ok.

2 likes

Please or to participate in this conversation.