Eloquent Where using CASE WHEN and withMin()

Question

I have a query like this:

Product::withMin('promotions as promotion_price', 'promotion_products.price')
  ->orderByRaw('CASE WHEN promotion_price IS NULL THEN sale_price ELSE promotion_price END ASC')
  ->get();

the query result like this:

[
  {
    "id": 5,
    "title": "Asus Nvidia RTX 3080",
    "sale_price": 42500000,
    "promotion_price": 10000
  },
  {
    "id": 14,
    "title": "Microsoft 365 Business Basic",
    "sale_price": 39100,
    "promotion_price": null
  },
  {
    "id": 3,
    "title": "Test Paimon s",
    "sale_price": 99999,
    "promotion_price": 50000
  },
  {
    "id": 4,
    "title": "Asus Nvidia RTX 3090",
    "sale_price": 56500000,
    "promotion_price": 50000
  },
  {
    "id": 2,
    "title": "Test Product",
    "sale_price": 990000,
    "promotion_price": 50000
  },
  {
    "id": 17,
    "title": "Microsoft 365 Apps",
    "sale_price": 129000,
    "promotion_price": null
  },
  {
    "id": 15,
    "title": "Microsoft 365 Business Standard",
    "sale_price": 156400,
    "promotion_price": null
  }
]

then I want to add some condition if promotion_price is null use sale_price to filter. I try use this query:

whereRaw('CASE WHEN promotion_price IS NULL THEN sale_price ELSE promotion_price END BETWEEN 0 AND 20000000')

and it said "Unknown Column 'promotion_price'". Any solution for this? thanks~

Answer 1

use havingRaw instead of where condition

Product::withMin('promotions as promotion_price', 'promotion_products.price')
        ->havingRaw('CASE WHEN promotion_price IS NULL THEN sale_price=>0 AND sale_price<=20000000 ELSE promotion_price=>0 AND promotion_price<=20000000 END')
        ->orderByRaw('CASE WHEN promotion_price IS NULL THEN sale_price ELSE promotion_price END ASC')
        ->get();