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

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 );
}])

Answer 4

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

this worked for me

Answer 5

for laravel 8 use this instead

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

this will return events that only with partcipats with user id 1 with that event relastionship,

Answer 6

I created a custom query scope in BaseModel (my all models extends this class):

/**
 * Add a relationship exists condition (BelongsTo).
 *
 * @param  Builder        $query
 * @param  string|Model   $relation   Relation string name or you can try pass directly model and method will try guess relationship
 * @param  mixed          $modelOrKey
 * @return Builder|static
 */
public function scopeWhereHasRelated(Builder $query, $relation, $modelOrKey = null)
{
    if ($relation instanceof Model && $modelOrKey === null) {
        $modelOrKey = $relation;
        $relation   = Str::camel(class_basename($relation));
    }

    return $query->whereHas($relation, static function (Builder $query) use ($modelOrKey) {
        return $query->whereKey($modelOrKey instanceof Model ? $modelOrKey->getKey() : $modelOrKey);
    });
}

You can use it in many contexts for example:

Event::whereHasRelated('participants', 1)->isNotEmpty(); // where has participant with id = 1 

Furthermore, you can try to omit relationship name and pass just model:

$participant = Participant::find(1);
Event::whereHasRelated($participant)->first(); // guess relationship based on class name and get id from model instance

Answer 7

[OOT]

A bit OOT, but this question is the most closest topic with my question.

Here is an example if you want to show Event where ALL participant meet certain requirement. Let's say, event where ALL the participant has fully paid. So, it WILL NOT return events which having one or more participants that haven't fully paid .

Simply use the whereDoesntHave of the others 2 statuses.

Let's say the statuses are haven't paid at all [eq:1], paid some of it [eq:2], and fully paid [eq:3]

Event::whereDoesntHave('participants', function ($query) {
   return $query->whereRaw('payment = 1 or payment = 2');
})->get();

Tested on Laravel 5.8 - 7.x

Answer 8

for laravel 8.57+

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