Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hi Here is my orders table structure.
1d|user_id|order_status|grand_total
i want to sort orders by their status suppose order by "pending" status should fetch all orders but pending orders should be at top and rest should be after that.
I can not simply order by status desc or asc b/c order statuses are in string/keywords
Just suggestion, it is better to use tiny integer instead.
If you want to keep this way, you will need to use order by field() function
Example: You will have to list out all the statuses, else mysql will order the unlisted status first.
Order::orderByRaw('FIELD(order_status,pending,deliver,expired) asc')->get();
@frankielee this works fine .
but in my scenario i am geting a sortby parameter in my api with different value each time.
eg sortby=pending , or sort_by=completed.
according to your solution i have to first sort all my statuses in a separate array according to sort_by parameter and then implode that array in my query
$statuses = ['pending','canceled','completed'] // when sort by=pending
$statuses = ['canceled','pending','completed'] // when sort by=canceled
Order::orderByRaw("FIELD(order_status,implode(',' ,$status)) asc")->get();
@sarmadindhar yeah, you will have to sort the array first. You have no other choices I guess
@sarmadindhar you can do like so
orderByRaw("if(order_status = ?, 0, 1) asc, order_status asc", [$status])
specified status will be at the top, others - sorted alphabetically
@SilenceBringer wow, you can do something like that @@
@frankielee MySQL have a lot of magic for us :-D
Please or to participate in this conversation.