overlapping periods scope + how to nest orWhere in laravel query builder

laravel-wheres-orWheres.php

<?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);
        });
}