Select query between two values in Laravel

Question

I'm trying to extrat the number of a product which prices between 1 and the max product price using Laravel and query builder.

I tried with this query in phpmyadmin and it returns a result:

SELECT product_id ,      count( user_id ) , sum( monies ) 
   FROM products, prices
   WHERE monies
   BETWEEN '20'
   AND products.price 

   AND products.id = 101
   GROUP BY (product_id);

But in PHP I coudn't specify the range on which we have to search :

$data = Product::join('prices', 'prices.product_id', '=', 'products.id')
                ->select(DB::raw('count( user_id ) as nombre'),DB::raw('sum(monies ) as sum'))
                ->where('prices.project_id', '=', '101')
                ->whereBetween('monies', array(1, 'products.price'))
                ->groupBy('prices.product_id')
                ->get();

But I always get an empty result and when I change whereBetween('monies', array(1, 'products.price')) with whereBetween('monies', array(1, 1000)) it returns a result.

How can I use the whereBetween?

damiani · Accepted Answer · 2014-11-03 18:26:28Z

2

whereBetween requires the second element of the values array to be either a static value or a variable, but not a column from your table. You can use two whereRaw statements (there is no whereBetweenRaw equivalent), which, when used together, will create and and condition, i.e.:

    ->whereRaw('monies >= 1')->whereRaw('monies <= products.price')

DB::raw won't work in this scenario, and two regular where clauses won't work either.

answered Nov 3, 2014 at 18:26

damiani

7,4212 gold badges26 silver badges24 bronze badges

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

Comments

JasonK · Accepted Answer · 2014-11-03 16:23:16Z

0

That's weird, you're using whereBetween() correctly as far as I know. Two things you could try:

Unquote products.price in the array
Simply use two where clauses (>= && <=)

Hope this helps.

answered Nov 3, 2014 at 16:23

JasonK

5,2949 gold badges36 silver badges62 bronze badges

1 Comment

Dev DOS Over a year ago

the problem is not related to the whereBetween it's related to the using of 'products.price' in the whereBetween clause.

ceejayoz · Accepted Answer · 2014-11-03 16:31:21Z

0

Chances are products.price is being escaped as a string. Try DB::raw('products.price').

answered Nov 3, 2014 at 16:31

ceejayoz

181k42 gold badges311 silver badges382 bronze badges

2 Comments

Dev DOS Over a year ago

it doesn't work also with DB::raw('products.price')

ceejayoz Over a year ago

Take a look at the query it's generating by doing toSql() instead of get().

Amiralidev · Accepted Answer · 2017-11-03 09:38:50Z

0

whereBetween() just accept integer maybe your variable is string.

answered Nov 3, 2017 at 9:38

Amiralidev

11 bronze badge

Collectives™ on Stack Overflow

Select query between two values in Laravel

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related