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"

Look at this post on the Laravel forums : forums.laravel.io/viewtopic.php?pid=46789#p46789 — user2629998
– user2629998, Commented Nov 27, 2013 at 0:39

The Alpha · Accepted Answer · 2013-11-27 00:49:14Z

31

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.

answered Nov 27, 2013 at 0:49

The Alpha

147k30 gold badges294 silver badges313 bronze badges

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

4 Comments

Shaft jackson Over a year ago

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

The Alpha Over a year ago

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

tdc Over a year ago

@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

manoos Over a year ago

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

user1669496 · Accepted Answer · 2013-11-27 18:17:18Z

8

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.

edited Nov 27, 2013 at 18:17

answered Nov 27, 2013 at 18:11

user1669496

33.3k9 gold badges76 silver badges65 bronze badges

2 Comments

Rafael Over a year ago

Does laravel clean this for you?

RattleyCooper Over a year ago

@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.

Somnath Muluk · Accepted Answer · 2015-04-20 10:37:48Z

0

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

answered Apr 20, 2015 at 10:37

Somnath Muluk

58.3k39 gold badges225 silver badges230 bronze badges

Comments

mariordev · Accepted Answer · 2016-02-18 00:15:40Z

0

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

answered Feb 18, 2016 at 0:15

mariordev

1095 bronze badges

Collectives™ on Stack Overflow

Laravel 4, eloquent - between statement and operators

4 Answers 4

4 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related