SQL BETWEEN Two Columns in Laravel/Lumen

Question

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 1

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