Custom Order By - 00 first, followed by 0,1,2,3... - Laravel / MySQL

Question

Take the following values

ABC3
ABC2
ABC1
ABC4
ABC0
ABC00

I have the following

return $this->hasMany('Item')
    ->orderBy('code');

Which returns the items in the following sequence:

ABC0
ABC00
ABC1
ABC2
ABC3
ABC4

But I want ABC00 at the top of the list, what's the best way to do this?

Answer 1

You are looking for a SQL statement [MYSQL flavor] like

SELECT code 
    FROM [some_table] 
ORDER BY CAST(SUBSTRING(code, 4) AS UNSIGNED) ASC, LENGTH(code) DESC;

In Eloquent, this will be

return $this->hasMany('Item')
    ->orderByRaw('CAST(SUBSTRING(code, 4) AS UNSIGNED) ASC, LENGTH(code) DESC');