Handling date ranges

I have a validity table with a start and end date field and I can think of the following 3 states below

unused, i.e. start = null
valid, i.e. start <= now() and end = null or end > now()
expired, i.e. start < end < now()

Suppose my subscription table need to left join it, is there an eloquent/SQL shortcut to find only those in use?
Or is there a better database structure to capture the 3 states?

Currently what I have is

->leftJoin('validity', function ($join) {
    $join
    ->on('validity.subscription_id', '=', 'subscriptions.id')
    ->on('validity.start', '<=', DB::raw('NOW()'))
    ->on('validity.end', '>', DB::raw('NOW()'))
    // line below does not work as intended, it generates "(validity.end is ...) = ``"
    ->on(DB::raw('(validity.end is null OR validity.end > NOW())')) 
})

Thank you.

laracoft

4 years ago

Best Answer

Level 27

Found it, but I'm still open to anyone who has better code.

->leftJoin('validity', function ($join) {
    $join
    ->on('validity.subscription_id', '=', 'subscriptions.id')
    ->on('validity.start', '<=', DB::raw('NOW()'))
    ->on(function ($join) {
        $join->whereNull('validity.end')
            ->orWhere('validity.end', '>', DB::raw('NOW()'));
    })
})

Please or to participate in this conversation.