Instantly share code, notes, and snippets.

Embed
What would you like to do?
overlapping periods scope + how to nest orWhere in laravel query builder
<?php
// Freek posted a query today, that drives OhDearApp filters (the app recommended!):
// @link https://twitter.com/freekmurze/status/972299131497713664
// We can make the query more eloquent, so why don't we use a scope!
// I
// Let's make it really eloquent and get here:
$site->downtimePeriods()
->overlapping($period)
->orderBy('started_at')
->get();
// II
// In order to achieve that, we would create this scope:
public function scopeOverlapping($query, Period $period) {
return $query->where('started_at', '<=', $period->end)
->where('ended_at', '>=', $period->start);
}
// Credits to to https://twitter.com/hulkur for simplifying the query further:
// https://twitter.com/hulkur/status/986521536914968576
// III
// The scope has a bit different structure than the original query of yours and here's why:
//
// to cover all possible cases of overlapping periods
// AND avoid issues in case there are GLOBAL SCOPES applied on the query
//
// this is the structure we would use:
$site
->downtimePeriods()
// one nested where to catch any of the OR clauses
->where(function ($query) use ($period) {
$query
->whereBetween('started_at', [$period->start, $period->end])
->orWhereNull('ended_at')
// AND clauses don't need nesting:
->orWhere('started_at', '<=', $period->start)->where('ended_at', '>=', $period->end);
})
->orderBy('started_at')
->get();
// IV
// Finally, the scope itself could be abstracted a bit to make it more generic and use on any model:
/**
* Overlapping periods scope - finds all cases:
* - records started within requested PERIOD
* - records ended within requested PERIOD
* - records covering whole requeted PERIOD (started before and ended after)
*/
public function scopeOverlapping($query, Period $period, string $start_col, string $end_col) {
return $query->where(function ($query) use ($period, $start_col, $end_col) {
$query
->whereBetween($start_col, [$period->start, $period->end])
->orWhereBetween($end_col, [$period->start, $period->end])
->orWhere($start_col, '>', $period->start)->where($end_col, '<', $period->end);
});
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment