CODEWITHSUNDEEP
mysqlsqldatabaselaraveleloquent
piyushkantm
piyushkantm

Reputation: 654

Custom Sorting in Laravel depending on column value

I want to sort a table entries. Here is the Eloquent Model.

namespace App;

use Illuminate\Database\Eloquent\Model;

/**
 * App\Story
 ...
 * @property string $due_date
 * @property string $status late|scheduled|completed
 ...
 */
class Story extends Model {
    ...
}

The order in which i want to return the Stories are:

So Let's assume there are 15 entries in db for each status type of story. and the Pagination limit is set to be 20.

so here is the response of each page

Please do let me know if the information provided above is not sufficient. Thanks!

Upvotes: 1

Views: 1999

Answers (2)

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

Not ideal, but works without a factor column:

Story::orderByRaw('FIELD(status, "late", "scheduled", "completed")')
    ->orderByRaw('(CASE WHEN status = "late" THEN 1 ELSE -1 END) * due_date')
    ->paginate(10);

When multiplied by an integer, due_date automatically gets converted to a string (20180609). So it should be faster than timestamp(due_date).

Upvotes: 1

user9888683
user9888683

Reputation:

In some cases, you may want to use a custom order sql for a specific column. To achieve this, you can use orderColumn api.

In this example, we will order the column name with nulls as last result.

use DataTables;

Route::get('user-data', function() {
$model = App\User::query();

return DataTables::eloquent($model)
            ->orderColumn('name', '-name $1')
            ->toJson();
});

Upvotes: 0

Related Questions