69

I am trying to find an elegant way in Eloquent and Laravel to say

select * from UserTable where Age between X and Y

Is there a between operator in Eloquent (I can't find it).

The closest i have gotten so far is chainging my query like this

$query->where(age, '>=', $ageFrom)
      ->where(age, '<=', $ageTo);

I also came across whereRaw that seems to work

$query->whereRaw('age BETWEEN ' . $ageFrom . ' AND ' . $ageTo . '');

Is there an actual Eloquent way (not raw) that deals with ranges?

GRowing
  • 4,629
  • 13
  • 52
  • 75

2 Answers2

146
$query->whereBetween('age', [$ageFrom, $ageTo]);

Look here: http://laravel.com/docs/4.2/queries#selects

Still holds true for Laravel 5: https://laravel.com/docs/5.8/queries#where-clauses

jsphpl
  • 4,800
  • 3
  • 24
  • 27
  • Thank you! I will look over the selects carefully :) – GRowing Nov 04 '14 at 23:53
  • What could be the equivalent using eloquent to: select * from UserTable where X between column1 and column2 – Brian Feb 07 '18 at 15:43
  • @Brian i think you would have to use `whereRaw()`, in case X is a binding value. If X is a column name, then you could use `->whereColumn('X', '<=', 'column1')->where('X', '=>', 'column2')`, which could also be written in a single `whereColumn([['X', '<=', 'column1'], ['X', '=>', 'column2']])` – jsphpl May 17 '19 at 08:25
5

The whereBetween method verifies that a column's value is between two values:

$users = DB::table('users')->whereBetween('votes', [1, 100])->get();

The whereNotBetween method verifies that a column's value lies outside of two values:

$users = DB::table('users')->whereNotBetween('votes', [1, 100])->get();

Laravel

Ali Raza
  • 243
  • 4
  • 11