Custom Sorting in Laravel depending on column value

Question

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:

"late" is ASC (Older first)
"scheduled" in DSC ( New first)
"completed" in DSC ( New first)

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

First Page: 15 Late Stories in ASC order + first 5 Scheduled Stories in DSC order
Second Page: Remaining 10 Scheduled Stories + first 10 Completed Stories in DSC order
Third Page: remaining 5 Completed Stories

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

@JonasStaudenmeir I have a working solution. But there are two issues with it: 1. Highly unoptimised and won't work for more complex sorting ( Which our project will be needing sooner or later) — piyushkantm
– piyushkantm, Commented Jun 8, 2018 at 18:07
So the working Solution is: add a number column factor that stores -3 for late and 2 for scheduled and 1 for completed. Then while maintaining rows, update the values whenever values of type is updated.. and the query will be sort timestamp(due_date) * factor DSC — piyushkantm
– piyushkantm, Commented Jun 8, 2018 at 18:18

Jonas Staudenmeir · Accepted Answer · 2018-06-09 01:25:26Z

1

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).

answered Jun 9, 2018 at 1:25

Jonas Staudenmeir

26k7 gold badges70 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

piyushkantm Over a year ago

This is exactly what i was looking for. Will give it a try. Thanks

user9888683 · Accepted Answer · 2018-06-08 11:59:24Z

0

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

answered Jun 8, 2018 at 11:59

user9888683

1 Comment

piyushkantm Over a year ago

Hi, Your answer mentions about a single column sorting. I want to sort considering two columns that too order of second column depends on the value of first column. Please do let me know if you have a solution for that. Thanks

Collectives™ on Stack Overflow

Custom Sorting in Laravel depending on column value

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related