Menu
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?
873
Laravel eager loading. What is eager loading? Eager loading is a concept in which when retrieving items, you get all the needed items together with all (or most) related items at the same time. This is in contrast to lazy loading where you only get one item at one go and then retrieve related items only when needed.
The main difference between eager and lazy loading is eager loading get all data with relationship records in single query and lazy loading require N+1 queries for getting main model and relation data. Eager loading run single query whereas lazy loading run N+1 queries.
each loop we execute another select query and getting all comments. so when ever we are displaying 50 records then it's fire 50 query behind. But we can prevent and instead of this more queries we can fire only one query and save database memory using Laravel Eager Loading.
Eager loading is merely fetching records preemptively. For example, when the author records are requested, you can aggregate their IDs. Then, you can query for only the books used by those authors beforehand.
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
89
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();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
