使用laravel使用2列查询排序

Question

Here is the sample of my database structure:

id |    name    |   role   |   login_status   |
-----------------------------------------------
 1      mark        user           1
 2      john       lawyer          0
 3      david       user           0
 4      erik       lawyer          0

What I want is to order them in such a way that the users with the lawyer role will be on the top of the list followed by the users with the value of 1 in the login_status column.

I used orderBy('login_status','desc') for the login_status column but i am having a hard time with the condition of the role column.

expected output:

- john
- erik
- mark
- david

Any help would be appreciated. Thanks.

Answer 1

You could use a case expression for you criteria

->orderByRaw("CASE WHEN role ='lawyer' THEN 1 ELSE 0 END DESC")
->orderBy( 'login_status', 'DESC' );

Plain sql would be like

select *
from demo
order by CASE WHEN role ='lawyer' THEN 1 ELSE 0 END DESC, login_status DESC

Demo

Or you can make your order clause even shorter like

order by role ='lawyer' DESC, login_status DESC

Demo