Laravel whereBetween在两列中

Question

i need to select products from DB by whereBetween. I have two double type columns in database, price and newprice. Price field is required and newprice is 0 by default. Admin writes newprice if there is any discounts on that product.

Now my problem is it that i need to select from DB with whereBetween by price if newprice == 0 and by newprice if newprice != 0. In a word filter by newprice if it is written, and by price if newprice is 0 by default.

Here is my code.

$from = (int)$request->priceFrom;
$to = (int)$request->priceTo;

$products = Product::whereIn('category_id', $childCategories)->orWhere('category_id', $category->id)->where('publish', 1)->orderBy('created_at', 'desc');
$products->whereBetween('price',array($from,$to))->orWhereBetween('newprice',array($from,$to));
$products = $products->paginate(15);

It doesn't works correctly ofc. Any ideas?

Answer 1

you can do something like this using laravels parameter grouping

$products->where(function($query){
     $query->where('new_price', 0)
     ->whereBetween('price', [$from, $to]);
})->orWhere(function($query){
     $query->where('new_price', '>', 0)
     ->whereBetween('new_price', [$from, $to]);
})->paginate(15);