CODEWITHSUNDEEP
mysqlsqllaravellaravel-5
Anuj Todankar
Anuj Todankar

Reputation: 85

Get all data, but show pending data first then Approved data and rejected data

I am writing a sql query in Laravel to fetch all the data for current year but condition is I want to get status - Pending data first and Approved and Rejected data. According to 'status' = Pending/Approve and Reject?

$leaves = Leave::whereRaw('YEAR(start_date) = YEAR(CURDATE()) AND YEAR(end_date)=YEAR(CURDATE())')
                 ->where('created_by', '=', \Auth::user()->creatorId())->get();

Upvotes: 0

Views: 1064

Answers (2)

divyesh makvana
divyesh makvana

Reputation: 449

You can use case in order by query. Please change your query to

$leaves = Leave::whereRaw('YEAR(start_date) = YEAR(CURDATE()) AND YEAR(end_date)=YEAR(CURDATE())') 
->where('created_by', '=', \Auth::user()->creatorId())
->orderByRaw('case when `status` LIKE "%Pending%" then 1 when `status` LIKE "%Rejected%" then 2 when `status` LIKE "%Approved%" then 3 else 4 end')
->get();

Upvotes: 1

Polus Fansare
Polus Fansare

Reputation: 9

Try to add "orderBy()" before the "get()" method of the query.

Like this:

$leaves = Leave::whereRaw('YEAR(start_date) = YEAR(CURDATE()) AND YEAR(end_date)=YEAR(CURDATE())')
->where('created_by', '=', \Auth::user()->creatorId())
->orderBy('approved', 'desc')
->get();

Note: Assuming approved as a column in your table where it has boolean value (0 or 1).

Upvotes: 1

Related Questions