'MySQL order by field in Eloquent

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' );
phpmysqlsqleloquent


Solution 1:[1]

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();

Solution 2:[2]

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();

Solution 3:[3]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 lukasgeiter
Solution 2 Rizwan Saleem
Solution 3 HadiNiazi

Related Questions