SQL BETWEEN Two Columns in Laravel/Lumen

Below is an excerpt from the Laravel documentation:

The whereBetween method verifies that a column’s value is between two values:

$users = DB::table('users')->whereBetween('votes', [1, 100])->get();

But what if I want to find out if a value is between two columns in my database?

This is my raw SQL:

SELECT a.*, b.name FROM restaurants a, restaurant_class b
WHERE a.restaurant_class_id = b.id
AND '$d' = CURRENT_DATE
AND '$t' BETWEEN a.saturday_ot AND a.saturday_ct
ORDER BY id DESC 

saturday_ot and saturday_ct are TIME columns in my table and $t is a time variable. So I want to check if the time is in between the the times in both columns.

Answer

There is no alternative to the whereBetween method that applies to two columns. You can however do this in one of two ways:

1. Use whereRaw with bindings, where you use the raw condition and a binding for the variable:

whereRaw('? between saturday_ot and saturday_ct', [$t])

2. Use a where with two conditions that use the two column values as boundaries for the $t variable value:

where(function ($query) use ($t) {
    $query->where('saturday_ot', '<=', $t);
    $query->where('saturday_ct', '>=', $t);
})