CODEWITHSUNDEEP
phpmysqllaraveleloquentquery-builder
Shaft jackson
Shaft jackson

Reputation: 105

Laravel 4, eloquent - between statement and operators

There is a query I use to run in mysql :

select * from my_table where $val between col1 and coL2;

It works fine, but with laravel 4, the only way to make that query is to have something like

my_model::where('col1','>=',$val)->where('col2','<=',$val)

This way doesn't seem to work, because I don't have the same result when using the usual "select * ..."

Any idea ?

Just to clarify my request : In my case i dont have "...where column between value1 and value2" but "where value between commun" So it seems to me that i can't use "wherebetween"

Upvotes: 8

Views: 15414

Answers (4)

mariordev
mariordev

Reputation: 109

Your eloquent example using where() didn't work because you have the comparison operators reversed. If you want to retrieve rows where val is between col1 and col2, it should be like this:

my_model::where('col1','<=',$val)->where('col2','>=',$val)

Notice the comparison operators are reversed to say "where val is greater than or equal to col1 and val is less than or equal to col2."

You may have to squint a little hard to see it. :)

Upvotes: 0

Somnath Muluk
Somnath Muluk

Reputation: 57766

Without creating MySQL Model, we can generate query like:

// If column value need to checked between value 1 and value 2
$DBConnection->table('users')->whereBetween('id', array(3, 6))->get();

// If value need to checked between column 1 and column 2 value
$DBConnection->->table('users')->whereRaw("$val between col1 and col2")->get();

Upvotes: 0

user1669496
user1669496

Reputation: 33078

This should do it...

$results = my_model::select('*')->whereRaw("$val between col1 and coL2")->get();

I think this is pretty safe, but you may need to clean $val first.

Upvotes: 9

The Alpha
The Alpha

Reputation: 146219

You may try something like this

// Get records whose id between 3 and 6
$users = User::whereBetween('id', array(3, 6))->get();

Or using variable

$id = 'id';
$from = 1;
$to = 5;
$users = User::whereBetween($id, array($from, $to))->get();

This will get all the records whose ID between 1 and 5.

Upvotes: 31

Related Questions