How to get MAX and MIN of two column in laravel

Question

I have table like in the image, a product with many prices and sales. I want to get max price for that product. If I used select(\DB::raw("max('price') as price"), 'products.*') that will return 800 which is wrong, because it has sale with 500 so I need to return 750. How Can I do this using laravel? any ideas!?

Answer 1

Ex: Products has many relationship product_detail pls try

select(\DB::raw("MAX(IF(product_detail.sale_price > 0, product_detail.sale_price, product_detail.price)) AS 'max'"))