CODEWITHSUNDEEP
phpmysqllaraveleloquent
behz4d
behz4d

Reputation: 1849

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 :(

Upvotes: 1

Views: 51

Answers (3)

OMR
OMR

Reputation: 12188

i recommend using where with clouser like this:

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

Upvotes: 1

STA
STA

Reputation: 34688

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

Upvotes: 2

Mohammad Aliyari
Mohammad Aliyari

Reputation: 126

you can use whereRaw instead of where or where(DB::raw('coalesce(price, discounted_price') <= 50))

also be carful to close your where by ' character

Upvotes: 1

Related Questions