SQL BETWEEN Two Columns in Laravel

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.

I think you have to use whereRaw method

alberto-bottarini
– alberto-bottarini

2016-01-22 16:06:44 +00:00
Commented Jan 22, 2016 at 16:06 — alberto-bottarini
– alberto-bottarini, Commented Jan 22, 2016 at 16:06

Bogdan · Accepted Answer · 2016-01-22 17:05:30Z

52

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

answered Jan 22, 2016 at 17:05

Bogdan

44.7k12 gold badges130 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL BETWEEN Two Columns in Laravel

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related