Mysql where clause on multiple fields which one might be null

In my products table, I have 2 columns: price and discounted_price in which discounted_price is null most of the time unless there is a promotion for that product:

+----+--------------------+-------+------------------+
| id | name               | price | discounted_price |
+----+--------------------+-------+------------------+
|  1 | test 1             | 79.00 |       null       |
|  2 | test 2             | 50.00 |       45.00      |
+----+--------------------+-------+------------------+

The product with id of 2 is now on promotion and has discounted_price. Now I want to query products to get products cheaper than 50 but in my query I need to check if discounted_price is null then look into price, otherwise look into discounted_price. What Google said is using:

$products = Product::where('coalesce(price, discounted_price) <= 50);

But it’s not working 🙁

Answer

Alternative, you can use sub query, like this :

$q = 50;
$product = Product::where(function($query) use($q) {
      $query->where('discounted_price', '<=', $q)
            ->orWhere('price', '<=', $q)
   })
   ->get();

Advertisement

Answer