CODEWITHSUNDEEP
phpmysqllaravellaravel-5eloquent
Tom Kur
Tom Kur

Reputation: 2388

OrderBy Coupons based on expiration status Laravel

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?

Upvotes: 1

Views: 899

Answers (3)

TsaiKoga
TsaiKoga

Reputation: 13394

Just use ORDER BY coupon_status ASC, created_at DESC'

This expired_at can compared with CURDATE():

Try this query:

$coupons= Coupon::where('status', 2)
                ->orderBy(\DB::raw('(IF((expired_at IS NULL OR expired_at < CURDATE()), 1, 0 )) ASC, created_at DESC'))
                ->get();

Upvotes: 1

Ali Ghaini
Ali Ghaini

Reputation: 905

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

Upvotes: 3

Mitesh Rathod
Mitesh Rathod

Reputation: 1059

You can also write like this:

$coupons= Coupon::where('status', 2)->orderBy([ 'coupon_status' => 'ASC', 'created_at' => 'DESC' ])->get();

I hope it will help you :)

Upvotes: 1

Related Questions