whereOverlaps() to check if two timeframes overlap

[mankowitz]

I often have to compare two timeframes (e.g. appointments) to see if they overlap. In Carbon, there is CarbonPeriod::overlaps(), and I'd like to build something like that for Builder. Something like this:

Appointment::whereOverlaps(['apt_start', 'apt_end'], ['2024-09-12', '2024-09-13'])->get()

This would generate SQL which looks like this

SELECT .... FROM appointments WHERE '2024-09-13' > apt_start AND '2024-09-12' < apt_end

There would also have to be a whereColumnsOverlap(['apt_start', 'apt_end'],['col2_start','col2_end']) that would generate SQL like

SELECT .... FROM appointments WHERE col2_end > apt_start AND col2_start < apt_end

I'm not sure, but I think I'd have to make the following changes:

In framework/src/Illuminate/Database/Query/Grammars/Grammar.php, I'd add

protected function whereOverlaps(Builder $query, $where)
    {
        $col1Start = $this->wrap(is_array($where['columns']) ? reset($where['columns']) : $where['columns'][0]);
        $col1End = $this->wrap(is_array($where['columns']) ? end($where['columns']) : $where['columns'][1]);
        $start = $this->parameter(is_array($where['values']) ? reset($where['values']) : $where['values'][0]);
        $end = $this->parameter(is_array($where['values']) ? end($where['values']) : $where['values'][1]);

        return  $where['not'] ? 
          ('(' . $end . '<=' . $col1Start . ' OR ' . $start . ' >' . $col1End . ')') :
          ('(' . $end . '>' . $col1Start . ' AND ' . $start . ' <' . $col1End . ')');
    }

    protected function whereColumnsOverlap(Builder $query, $where)
    {
        $col1Start = $this->wrap(is_array($where['columns']) ? reset($where['columns']) : $where['columns'][0]);
        $col1End = $this->wrap(is_array($where['columns']) ? end($where['columns']) : $where['columns'][1]);
        $col2Start = $this->wrap(is_array($where['compareColumns']) ? reset($where['compareColumns']) : $where['compareColumns'][0]);
        $col2End = $this->wrap(is_array($where['compareColumns']) ? end($where['compareColumns']) : $where['compareColumns'][1]);

        return  $where['not'] ? 
          ('(' . $col2End . '<=' . $col1Start . ' OR ' . $col2Start . ' >' . $col1End . ')') :
          ('(' . $col2End . '>' . $col1Start . ' AND ' . $col2Start . ' <' . $col1End . ')');
    }

In framework/src/Illuminate/Database/Query/Builder.php:

    public function whereOverlaps(array $columns, iterable $values, $boolean = 'and', $not = false)
    {
        $type = 'overlaps';

        if ($values instanceof CarbonPeriod) {
            $values = [$values->getStartDate(), $values->getEndDate()];
        }

        $this->wheres[] = compact('type', 'column', 'values', 'boolean', 'not');
        $this->addBinding(array_slice($this->cleanBindings(Arr::flatten($values)), 0, 2), 'where');
        return $this;
    }

    public function whereColumnsOverlap(array $columns, array $compareColumns, $boolean = 'and', $not = false)
    {
        $type = 'columnsOverlap';
        $this->wheres[] = compact('type', 'column', 'compareColumns', 'boolean', 'not');
        return $this;
    }

edit:fixed function name

[ghost]

You forgot to rename
public function whereBetween(array $columns, iterable $values, $boolean = 'and', $not = false

But you can do this via macros in both classes.