I have Movie and Event models. Movie has many events and Event belong to single Movie. What I want to do is to join events by date. I am using Spatie Query Builder (https://docs.spatie.be/laravel-query-builder/v2/) for filters and includes.
// Movie model
public function events(): HasMany
{
return $this->hasMany(Event::class);
}
// Event model
public function movie(): HasOne
{
return $this->hasOne(Movie::class);
}
// Movie Query
$this->allowedFilters([
AllowedFilter::custom('date', new FiltersEventDate),
]);
$this->allowedIncludes([
AllowedInclude::relationship('events'),
]);
// Movie resource
return [
'name' => $this->getTranslations('name'),
'slug' => $this->getTranslations('slug'),
'events' => $this->whenLoaded(
'events',
fn() => EventResource::collection($this->events),
),
];
For example:
date: '2020-07-01'
return: movies list with events by date (do not include events with another date)
What I tried:
$query->whereHas('events', function (Builder $query) use ($date) {
return $query->whereDate('starts_at', $date);
});
$query->with(['events', function (Builder $query) use ($date) {
return $query->whereDate('starts_at', $date);
}]);
$query->join('events', 'movies.id', '=', 'events.movie_id')
->whereDate('starts_at', $date);
It only works when I make relation like this, but this solution is not so good for me:
public function events(): HasMany
{
return $this->hasMany(Event::class)->where('starts_at', <date>);
}
Maybe I need to join movie on event, group by movie and then return event resource?