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 partecipants()
{
return $this->hasMany('App\Partecipant', 'IDEvent', 'ID');
}
public function owner()
{
return $this->hasOne('App\User', 'ID', 'IDOwner');
}
}
and
class Partecipant extends Model {
protected $table = 'partecipants';
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('partecipants')->where('IDUser', 1)->get();
but the where
condition is applied on the Event
and not on its Partecipants
. The following gives me an exception:
Partecipant::where('IDUser', 1)->event()->get();
I know that I can write this:
$list = Partecipant::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?
The correct syntax to do this on your relations is:
Event::whereHas('partecipants', function ($query) {
$query->where('IDUser', '=', 1);
})->get();
Read more at https://laravel.com/docs/5.8/eloquent-relationships#eager-loading
P.S. It's "participant", not "partecipant".
@Cermbo's answer is not related to this question. in this answer, laravel
will give you all Events
if per Event
has 'partecipants'
with IdUser
is 1
.
But if you want to get all Events
with all 'partecipants'
provided that per 'partecipants'
with IdUser
is 1, then you should do something like this :
Event::with(["partecipants" => function($q){
$q->where('partecipants.IdUser', '=', 1);
}])
attention to:
in where use your table name, no Model name.