CODEWITHSUNDEEP
phpmysqlsqleloquent
wout
wout

Reputation: 2577

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

Upvotes: 32

Views: 33645

Answers (3)

Hadayat Niazi
Hadayat Niazi

Reputation: 2480

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.

Upvotes: 0

Rizwan Saleem
Rizwan Saleem

Reputation: 386

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

Upvotes: 11

lukasgeiter
lukasgeiter

Reputation: 152900

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

Upvotes: 51

Related Questions