Eloquent - where date range overlaps, either before and/or after, another date range

Hi all,

Edited: made my question a bit clearer

I have events with start and end date fields. These will be displayed in a calendar month view, a marker for each day each event is on.

I then want to get all events that fall within or overlap, either before and/or after, a second date range (in this case, the start and finish dates of month I'm currently viewing).

For example, I'm viewing November in the calendar. I have an event that started in September and ends in December. This event should show up in November since it spans across it completely!

Now I have this working but it is not fully tested for all cases. However it is a bit cumbersome and I was wondering if I was over complicating it!

    $user = User::where('id', Auth::user()->id)
      ->with([ 'events' => function($query) use ($from, $to) {
        $query->where('start', '>=', $from)->where('end', '>=', $to)
              ->orWhere('start', '>=', $from)->where('end', '<=', $to)
              ->orWhere('start', '<=', $from)->where('end', '>=', $to)
              ->orWhere('start', '<=', $from)->where('end', '<=', $to)
              ->orWhere('start', $from)->where('end', $to);
      }])
      ->first();

Thanks :)

tylernathanreed

9 years ago

Level 14

As far as logic goes, you want this:

The Start Date of the Event happened before To
- i.e. The Start Date needs to be in the past from the perspective of To
AND The End Date of the Event happened after From
- i.e. The End Date needs to be in the future from the perspective of From

In this example, your query would be:

$user = User::where('id', Auth::user()->id)
  ->with([ 'events' => function($query) use ($from, $to) {
    $query->where('start', '<=', $to)->where('end', '>=', $from)
  }])
  ->first();

To clean things up a bit, I like to add "Time" Macros to all of my Queries:

AppServiceProvider:

use Carbon\Carbon;
use Illuminate\Database\Query\Builder;

public function boot()
{
    Builder::macro('past', function($column, $when = 'now', $strict = true) {

        $when = Carbon::parse($when);
        $operator = $strict ? '<' : '<=';

        return $this->where($column, $operator, $when);
    }

    Builder::macro('future', function($column, $when = 'now', $strict = true) {

        $when = Carbon::parse($when);
        $operator = $strict ? '>' : '>=';

        return $this->where($column, $operator, $when);
    }
}

This would allow you to make queries like this:

$user = User::where('id', Auth::user()->id)
  ->with([ 'events' => function($query) use ($from, $to) {
    $query->past('start', $to)->future('end', $from)
  }])
  ->first();

To take things a step further, I'd add this as a scope to your Event Model:

public function scopeOverlapping($query, $from, $to)
{
    return $query->past('start', $to)->future('end', $from);
}

Which further clarifies your original query to:

$user = User::where('id', Auth::user()->id)
  ->with([ 'events' => function($query) use ($from, $to) {
    $query->overlapping($from, $to);
  }])
  ->first();

Now then, a few optimizations:

Judging the context of this query, you're wanting to load the overlapping events onto the Authenticated User. Seeing as you already have the Model, it might make more sense to do this:

$events = Auth::user()->events()->overlapping($from, $to)->get();

If you really need the Authenticated User to be passed along with an $events variable, you can always do this:

$user = Auth::user()->setRelation('events', $events);

This could be inlined as:

$user = Auth::user()->setRelation('events', 
    Auth::user()->events()->overlapping($from, $to)->get()
);

This effectively does exactly the same thing, but I consider it to be more readable than a ->with construct using an array and a closure.

To clean things up even further, you could add the following function to your User Model:

public function loadOverlappingEvents($from, $to)
{
    return $this->setRelation('events', 
        $this->events()->overlapping($from, $to)->get()
    );
}

Seeing as the setRelation function returns the calling Model, you could now do this:

$user = Auth::user()->loadOverlappingEvents($from, $to);

$events = $user->events;

I know that I probably said more than what you needed, I just wanted to point out some design tips. Hopefully somewhere along writing all of this, I managed to answer your question.