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