How to sort database value without using ASC or DESC-CodePudding

I have an attribute in the database that I want to use a filter on by sorting it as ASC or DESC. I want the order of the elements to be

highest 
high
medium
low
lowest

Using the ASC $q->orderBy('priority', 'ASC'); the result given is

high
highest
low
lowest
medium

What can I do to sort the elements in that order? thanks

CodePudding user response：

You can use a case statement in order by:

order by 
case column_name
when 'highest' then 5
when 'high' then 4
when 'medium' then 3
when 'low' then 2
when 'lowest' then 1
end desc

So something like this should work:

$q->orderByRaw("case column_name
    when 'highest' then 5
    when 'high' then 4
    when 'medium' then 3
    when 'low' then 2
    when 'lowest' then 1
    end desc");

CodePudding user response：

What would probably be sensible is to use a numeric value to represent these values rather than literally storing the text in the column.

Then sorting by the number would be trivial using ORDER BY.

You can have a lookup table separately to match the IDs to the words.

CodePudding user response：

MySQL also has the convenience function FIELD for this use case:

$q->orderBy(DB::raw("FIELD(priority,'highest', 'high', 'medium', 'low', 'lowest')"))

FIELD returns the index of the first argument in the rest of the arguments as a number.