Order Results by Specific Values with orderByRaw() in Laravel

Some situations require developers to sort database results by specific or calculated values. orderByRaw() method will be useful.

To order results by specific values using orderByRaw() in Laravel, you need to construct a raw SQL expression that specifies the order you want. This method is beneficial when you have a custom sorting order that can’t be achieved with the regular orderBy() method.

Both Query Builder and Eloquent in Laravel support this method of querying using a MySQL raw statement.

For example, our database has a sales table that contains a state field. This field only has 4 values: DRAFT, SENT, DONE, and CANCEL. We want to list sales following this order, SENT, DRAFT, DONE, CANCEL.

Query Builder

$sales = DB::table('sales')
->orderByRaw("FIELD(state , 'SENT', 'DRAFT', 'DONE', 'CANCEL') ASC")
->get();

Eloquent

$sales = Sale::orderByRaw("FIELD(state , 'SENT', 'DRAFT', 'DONE', 'CANCEL') ASC")
->paginate(100);