Laravel where on relationship object

Question

I'm developing a web API with Laravel 5.0 but I'm not sure about a specific query I'm trying to build.

My classes are as follows:

class Event extends Model {

    protected $table = 'events';
    public $timestamps = false;

    public function participants()
    {
        return $this->hasMany('App\Participant', 'IDEvent', 'ID');
    }

    public function owner()
    {
        return $this->hasOne('App\User', 'ID', 'IDOwner');
    }
}

and

class Participant extends Model {

    protected $table = 'participants';
    public $timestamps = false;

    public function user()
    {
        return $this->belongTo('App\User', 'IDUser', 'ID');
    }

    public function event()
    {
        return $this->belongTo('App\Event', 'IDEvent', 'ID');
    }
}

Now, I want to get all the events with a specific participant. I tried with:

Event::with('participants')->where('IDUser', 1)->get();

but the where condition is applied on the Event and not on its Participants. The following gives me an exception:

Participant::where('IDUser', 1)->event()->get();

I know that I can write this:

$list = Participant::where('IDUser', 1)->get();
for($item in $list) {
   $event = $item->event;
   // ... other code ...
}

but it doesn't seem very efficient to send so many queries to the server.

What is the best way to perform a where through a model relationship using Laravel 5 and Eloquent?

Answer 1

The correct syntax to do this on your relations is:

Event::whereHas('participants', function ($query) {
    return $query->where('IDUser', '=', 1);
})->get();

This will return Events where Participants have a user ID of 1. If the Participant doesn't have a user ID of 1, the Event will NOT be returned.

Read more at https://laravel.com/docs/5.8/eloquent-relationships#eager-loading

Answer 2

@Cermbo's answer is not related to this question. In that answer, Laravel will give you all Events if each Event has 'participants' with IdUser of 1.

But if you want to get all Events with all 'participants' provided that all 'participants' have a IdUser of 1, then you should do something like this :

Event::with(["participants" => function($q){
    $q->where('participants.IdUser', '=', 1);
}])

N.B:

In where use your table name, not Model name.

Answer 3

for laravel 8.57+

Event::whereRelation('participants', 'IDUser', '=', 1)->get();

Answer 4

Use this code:

return Deal::with(["redeem" => function($q){
    $q->where('user_id', '=', 1);
}])->get();

Answer 5

With multiple joins, use something like this code:

$userId = 44;
Event::with(["owner", "participants" => function($q) use($userId ){
    $q->where('participants.IdUser', '=', 1);
    //$q->where('some other field', $userId );
}])