Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
overlapping periods scope + how to nest orWhere in laravel query builder
// Freek posted a query today, that drives OhDearApp filters (the app recommended!):
// @link
// 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:
// 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 for simplifying the query further:
// 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:
// one nested where to catch any of the OR clauses
->where(function ($query) use ($period) {
->whereBetween('started_at', [$period->start, $period->end])
// AND clauses don't need nesting:
->orWhere('started_at', '<=', $period->start)->where('ended_at', '>=', $period->end);
// 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) {
->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
You can’t perform that action at this time.