Laravel eager loading & whereHas

Question

Several questions have been asked about this but none provided a satisfactory explanation for this problem.

I run the following query:

$return = Vacation::whereHas('dates', function ($query) use ($from, $to) {
    $query->whereBetween('start_date', [$from, $to]);
})->get();

According to the query log, this produces the following SQL. The returned result is correct but there is a problem with the JOINed data.

select * from `vacations` where exists (select * from `vacation_dates` where `vacations`.`id` = `vacation_dates`.`vacation_id` and `start_date` between '2017-08-01 00:00:00' and '2017-08-30 00:00:00')

Since there's no JOIN, the related records are added afterwards through eager loading and the constraint is lost.

The scenario involves Vacations that have multiple start / stop dates and I want to check which Vacations have a start_date within the $start and $end date range.

If there's no occurrences, no records are returned.

When there is an occurrence, the Vacation is returned with ALL the dates and not just the ones in the constraint.

I understand how / what it's doing but don't see how I can get what I need: the record with the joined data that follows the constraint.

Anyone?

Answer 1

Solution:

$callback = function($query) use($from, $to) {
            $query->whereBetween('start_date', [$from, $to]);
        };
        $return = Vacation::whereHas('dates', $callback)->with(['dates' => $callback])->get();

Solution is from this SO post

Answer 2

Same answer as @stef gave, but better looking syntax (I believe)

$return = Vacation::query()
    ->whereHas('dates', $datesFilter = function($query) use ($from, $to) {
        $query->whereBetween('start_date', [$from, $to]);
    })
    ->with(['dates' => $datesFilter])
    ->get();