Filter/Query Eloquent child table

I have a table called 'events' (model: Event) and this has many "shifts" (Model: Shift)

How do I only get events with a shift date from this year? Eg: Event::with('shifts')->future();

I found various info on whereDate etc but this seems to apply to the parent model rather than the children.

I can do basic joins etc in Eloquent but am struggling to find resources on more advanced queries so any help with where to go with that also welcome! I'm coming from Codeigniter...

Thank you in advance

tykus

6 years ago

Best Answer

Level 104

A whereHas Eloquent query will check for the existence of the relation constrained to the given builder query

Event::whereHas('shifts', function ($builder) {
    $builder->whereYear('shift_date', date('Y'));
})->get();

Whatever is inside the Closure is "in the scope of" the related table - have a look at the resulting query to understand what this means.

1 like

Nakov

6 years ago

Level 73

@alunr you can try this:

Event::with('shifts')->whereHas('shifts', function($query) {
    $query->whereYear('created_at', date('Y'));
})->get();

1 like

AlunR

6 years ago

Level 3

Ok, that's starting to make a bit of sense now! If I can crack the Eloquent part things will be so much easier!

tykus

6 years ago

Level 104

Keep asking questions! If you understand what you're trying to do, there are people who can help you find the way to do it.

1 like

Please or to participate in this conversation.