Laravel 4, eloquent - between statement and operators

Question

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"

Rhiamon

Look at this post on the Laravel forums : forums.laravel.io/viewtopic.php?pid=46789#p46789 – Ruth

Answer 1

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.

Cestode

Does laravel clean this for you? – Smokeless

@Rafael, @user1669496, Laravel does not do anything to the variable. You need to call like this: $results = my_model::select('*')->whereRaw("? between col1 and coL2", ['someValue'])->get(); for a safe query. – Festoonery

Answer 2

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.

Divaricate

Thanks for replying. The problème in my case is that the input data in my query has to be between columns. – Rhiamon

I didn't get it, can you please be more descriptive ? – Divaricate

@WereWolf-TheAlpha you're hard-coding the "between" values array(3,6), but he needs to select between 2 columns in the table -- SELECT * FROM table WHERE $desiredValue BETWEEN col1 AND col2 – Superstitious

@TheAlpha $attendances= Attendance::whereBetween('attndate', array($fromdate, $todate))->get(); ..... this command is not working for dates?? is there any other solutions for comparing dates?? – Candlepin

Answer 3

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

Calcifuge

Answer 4

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. :)

Kestrel