Eloquent Sort By depend on the field

I have a query like this:

Product::withMin('promotions as promotion_price', 'promotion_products.price')
        ->get();

I want to sort the result but i have problem. if the result of withMin if null i want to sort it by another field (lets say ‘product.sale_price’). Or how can i make a new field, lets say ‘final_price’, if promotion_price is null use sale_price.

What i already tried:

add this after get()

        ->sortByDesc(function ($product){
            if($product->promotion_price === null)
            {
                return $product->sale_price;
            }
            else
            {
                return $product->promotion_price;
            }
        }

the result still not sorted

{
  "0": {
    "id": 2,
    "title": "Test Product",
    "sale_price": 990000,
    "promotion_price": 50000
  },
  "1": {
    "id": 3,
    "title": "Test Paimon s",
    "sale_price": 99999,
    "promotion_price": 50000
  },
  "2": {
    "id": 4,
    "title": "Asus Nvidia RTX 3090",
    "sale_price": 56500000,
    "promotion_price": 50000
  },
  "3": {
    "id": 5,
    "title": "Asus Nvidia RTX 3080",
    "sale_price": 42500000,
    "promotion_price": 10000
  },
  "4": {
    "id": 6,
    "title": "Gigabyte Nvidia RTX 3090",
    "sale_price": 53500000,
    "promotion_price": null
  },
  "5": {
    "id": 7,
    "title": "Gigabyte Nvidia RTX 3080",
    "sale_price": 40000000,
    "promotion_price": null
  },
  "6": {
    "id": 8,
    "title": "Zotac Nvidia RTX 3090",
    "sale_price": 49500000,
    "promotion_price": null
  }
}

Answer

use orderByRaw

 Product::withMin('promotions as promotion_price', 'promotion_products.price')
 ->orderByRaw("CASE WHEN promotion_price is null then sale_price else promotion_price end DESC" )
 ->get();

Advertisement

Answer