[FIXED] OrderBy Coupons based on expiration status Laravel

Issue

I need to display list of coupons and order it based on created_at date and expiration status.

The columns of my Coupons table

id
name
price
expired_at
created_at
status

My laravel query

$coupons= Coupon::where('status', 2)->orderBy('created_at', 'DESC')->orderBy( First I will need to compare expired_at with today date here)->get();

The problem is I dont know how to orderBy coupon_status, I want to display all expired coupons at bottom of the list and on-going coupons at top of the list and also orderBy created_at (the newest and on-going coupons is at the top of the list)

so probably need to create temporary column to get expiration status, before to order the list. Any Idea how to achieve this?

Solution

compare expired at with today (expired_at < NOW())

$coupons= Coupon::where('status', 2)->orderByRaw(
 "CASE WHEN expired_at IS NULL OR expired_at > CURDATE() THEN 1 ELSE 0 END DESC"
);

Answered By - Ali Ghaini