MySQL order by field in Eloquent

Question

When I want to define a custom sort order in a MySQL query I can do something like this:

ORDER BY FIELD(language,'USD','EUR','JPN')

What would be the Eloquent ORM version of that?

UPDATE:

This is the solution and it also works when ordering on various fields:

$events = Event::with( 'type', 'location' )
               ->orderBy( 'event_type_id' )
               ->orderByRaw( "FIELD(status, 'good', 'bad', 'hidden', 'active', 'cancelled')" )
               ->orderBy( 'date' );

did you try `$query->orderBy("FIELD(language,'USD','EUR','JPN')", 'asc');` ? — Alex, Apr 15 '15 at 19:52

score 49 · Accepted Answer · answered Apr 15 '15 at 19:47

49

Using either DB::raw() or orderByRaw directly should work:

$models = Model::orderByRaw('FIELD(language, "USD", "EUR", "JPN")')->get();
// or
$models = Model::orderBy(DB::raw('FIELD(language, "USD", "EUR", "JPN")'))->get();

answered Apr 15 '15 at 19:47

lukasgeiter

147,337
26
332
270

Aside from the raw order query I am also ordering on other fields. So I get this error now: ```SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'asc' at line 1 (SQL: select * from `events` where `events`.`deleted_at` is null and `status` = 'good' order by `event_type_id` asc, FIELD(status, 'good', 'bad', 'hidden', 'active', 'cancelled', `date` asc)``` – wout Apr 15 '15 at 20:01
Looks like you put `asc` inside the parenthesis – lukasgeiter Apr 15 '15 at 20:04
I updated the initial question to include the Eloquent syntax. – wout Apr 15 '15 at 20:06
Sorry, my bad. it seems like I forgot to add the closing parenthesis. Thanks for your answer. It works now. – wout Apr 15 '15 at 20:08
If in your case still doesn't work, try using the notation **table_name.field_name** (in this case «events.language» instead of only «language») as if there are repeated field names with other linked tables, it can give problems – Alberto Suárez Feb 08 '22 at 01:00
Is this limiting the results or just ordering them? If I order by something - lets say an id and I order by 2, 5, 7 - what will happen with the rest of the rows which have different ids? – alex toader Dec 20 '22 at 13:43

score 10 · Answer 2 · answered Mar 30 '20 at 11:55

Use implode in the second parameter of FIELD()

 $facilities = $query->with(['interest','city:id,name', 'state:id,name'])
        ->Active()
        ->whereIn('facility_id', $facilities_list)
        ->orderByRaw('FIELD(facility_id, '.implode(", " , $facilities_list).')')
        ->get();

score 1 · Answer 3 · answered Apr 26 '22 at 04:27

If you are using join you can simplay add table name before column.

Student::orderByRaw('FIELD(students.id, 3, 2, 1) DESC')->join('students_contact', 'students_contact.student_id', '=', 'students.id')
->get();

Note: Start adding a column in the raw query by the right side, let me explain with an example If you want to achieve the orders 7, 8, 9

orderByRaw('FIELD(students.id, 9, 8, 7) DESC')

Then it will fetch the data in the correct way. I also added the DESC keyword becuase it adds sorted data at the bottom of the list, not at starting of the list.

MySQL order by field in Eloquent

3 Answers3

Linked