CODEWITHSUNDEEP
laraveleloquent
stef
stef

Reputation: 27749

Laravel eager loading & whereHas

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?

Upvotes: 6

Views: 5070

Answers (2)

Yerke
Yerke

Reputation: 2215

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();

Upvotes: 5

stef
stef

Reputation: 27749

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

Upvotes: 9

Related Questions