Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

PaulJasiul's avatar

Join relations by relation value

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?

0 likes
4 replies
bobbybouwmann's avatar

You already have the answer in front of you, you simply need to combine them ;)

Movie::whereHas('events', function (Builder $query) use ($date) {
        return $query->whereDate('starts_at', $date);
    })
    ->with(['events', function (Builder $query) use ($date) {
        return $query->whereDate('starts_at', $date);
    }]);

The whereHas makes sure that it only returns the movies that actually have an event on the given date. The with makes sure it only returns the events that belong to the movie that is on that exact date.

Let me know if that works for you ;)

PaulJasiul's avatar

I tried this one but the problem was with AllowedInclude::relationship('events') because it loads all unfiltered events. Thanks :)

bobbybouwmann's avatar

@pauljasiul That's because you use the package to make this work. Using a package is great, but it also has limitations. With the given query you make it work, but that means you can't use the package anymore.

Please or to participate in this conversation.