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

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

Can anyone help me out yr. Thanks in advanced.

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

CodePudding user response：

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).

CodePudding user response：

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