sortable()

The sortable() scope makes it easy (and secure) to define columns the user can sort by.

It is loosely based on the column-sortable package, which we used to use, but it is more secure (because you have to declare the sortable columns in the controller) and more flexible.

Installation

Add this to the Model base class (or to a specific model if you prefer):

use Alberon\LaravelUtilities\Models\SortableScope;

class Model extends BaseModel
{
    use SortableScope;

    // ...
}

Basic Usage

Let's start with a basic example:

$examples = Example
    ::sortable([
        'name',
        'age',
        'id',
    ])
    ->paginate();

By default, this would result in this query:

SELECT *
FROM examples
ORDER BY age, name, id

If we add ?sort=age&direction=asc to the query string, the age column will be moved to the top of the list, which will result in ORDER BY age, name, id.

If we change it to ?sort=age&direction=desc, the query will change to ORDER BY age DESC, name, id. Note that only the age column is sorted in descending order - this is by design, as this usually gives more useful results than flipping everything.

The frontend logic required to generate the sort links can be seen in Nexus's <SortableLink> component. We haven't yet built a Blade version of this yet, but it would be possible to do so.

Default Descending

By default, all columns are sorted in ascending order. To sort a column descending by default, add DESC to the name (just like SQL):

$examples = Example
    ::sortable([
        'age DESC',
        'name',
        'id',
    ])
    ->paginate();

SELECT *
FROM examples
ORDER BY age DESC, name, id

This only changes the default value - it can still be overridden with ?direction=asc.

Queries With Joins

If the query has any joins, you should specify the column names in full to avoid ambiguities:

$examples = Example
    ::select('examples.*', 'statuses.name AS status')
    ->join('statuses', 'statuses.id', 'examples.status_id')
    ->sortable([
        'example.age',
        'example.name',
        'example.id', // Just specifying 'id' would be ambiguous
    ])
    ->paginate();

SELECT examples.*, statuses.name AS status
FROM examples
JOIN statuses ON statuses.id = examples.status_id
ORDER BY example.age, example.name, example.id

The query parameter is still just the column name without the table name - i.e. ?sort=age&direction=asc.

Column Aliases

If the internal names aren't user-friendly, you may want to alias one column name to another. You can do that using array keys:

$examples = Example
    ::sortable([
        'age' => 'date_of_birth DESC',
        'name',
        'id',
    ])
    ->paginate();

SELECT *
FROM examples
ORDER BY date_of_birth DESC, name, id

This is also useful when you want to sort by a column from another table:

$examples = Example
    ::select('examples.*')
    ->join('statuses', 'statuses.id', 'examples.status_id')
    ->sortable([
        'status' => 'statuses.position',
        'examples.name',
        'examples.id',
    ])
    ->with('status')
    ->paginate();

SELECT examples.*
FROM examples
JOIN statuses ON statuses.id = examples.status_id
ORDER BY statuses.position, examples.name, examples.id

To make it easier to read, you may prefer to give every column an alias even where it is optional:

$examples = Example
    ::select('examples.*')
    ->join('statuses', 'statuses.id', 'examples.status_id')
    ->sortable([
        'status' => 'statuses.position',
        'name' => 'examples.name',
        'id' => 'examples.id',
    ])
    ->with('status')
    ->paginate();

-- Same as above
SELECT examples.*
FROM examples
JOIN statuses ON statuses.id = examples.status_id
ORDER BY statuses.position, examples.name, examples.id

Sorting by Multiple Columns

Sometimes a column in the table is actually made up of more than one column of data. For those times, you can specify an array of columns to sort by:

$examples = Example
    ::sortable([
        'name',
        'date_range' => ['start_date', 'end_date'],
        'id',
    ])
    ->paginate();

SELECT *
FROM examples
ORDER BY name, start_date, end_date, id

When we add ?sort=date_range&direction=desc to the query, we get:

SELECT *
FROM examples
ORDER BY start_date DESC, end_date DESC, name, id

It is OK to include a column more than once:

$examples = Example
    ::select('examples.*')
    ->join('priorities', 'priorities.id', 'examples.priority_id')
    ->join('statuses', 'statuses.id', 'examples.status_id')
    ->sortable([
        'id' => 'examples.id',
        'status' => ['statuses.position', 'priorities.position'],
        'priority' => ['priorities.position', 'statuses.position'],
    ])
    ->with('priority', 'status')
    ->paginate();

By default this will generate ORDER BY examples.id, statuses.position, priorities.position, priorities.position, statuses.position - but MySQL knows the id is unique so it will be simplified to just ORDER BY examples.id. (In general, anything after the ID column will be ignored.)

When we sort by status it will generate ORDER BY statuses.position, priorities.position, examples.id, priorities.position, statuses.position, which MySQL simplifies to ORDER BY statuses.position, priorities.position, examples.id, removing the duplicates.

When we sort by priority it will generate ORDER BY priorities.position, statuses.position, examples.id, statuses.position, priorities.position, which MySQL simplifies to ORDER BY priorities.position, statuses.position, examples.id.

You can see an example of how this is useful on the Support Cases app (although it doesn't actually use this function). By default it sorts by ID (descending). But when you click the Priority column, it sorts by priority then status, which gives you you the most critical cases first, then ordered by status. Or when you click on Status it shows the new cases first, then ordered by priority.

Sorting NULL Values

In MySQL, a NULL value is always sorted first. That means if we use ORDER BY email ASC then we get all the NULL email addresses first and have to scroll through several pages to see any data - not usually what we want.

To make it easy to put the NULL values at the end, the sortable() function supports the following syntax:

$examples = Example
    ::sortable([
        'name',
        'email' => ['email IS NULL', 'email'],
        'id',
    ])
    ->paginate();

SELECT *
FROM examples
ORDER BY name, email IS NULL, email, id

This works because email IS NULL returns 1 for NULL values and 0 for non-NULL values, so the non-NULL values are moved to the top of the list.

Note: <column> IS NULL is a special case - arbitrary SQL isn't allowed here.

Conditionally Skipping Columns

Sometimes you don't want users to be able to sort by a particular column - e.g. if they don't have permission to view that column, it would be a security risk to let them sort by it (they could work out the value, or at least part of it, by comparing to other known records).

To allow this, any false or null columns will be skipped:

$examples = Example
    ::sortable([
        'name',
        'email' => Gate::allows(Permission::ViewEmailAddresses),
        'id',
    ])
    ->paginate();

Or if you are using aliases:

$examples = Example
    ::select('examples.*')
    ->join('statuses', 'statuses.id', 'examples.status_id')
    ->sortable([
        'status' => 'statuses.position',
        'name' => 'examples.name',
        'email' => Gate::allows(Permission::ViewEmailAddresses) ? 'examples.email' : false,
        'id' => 'examples.id',
    ])
    ->with('status')
    ->paginate();

(Or you could dynamically build up the array in some other way.)

Custom Sorters

For even more advanced cases, use a closure to provide your own logic:

$examples = Example
    ::sortable([
        'initials' => fn(Builder $query, $direction = 'asc') => $query
            ->orderByRaw("LEFT(first_name, 1) $direction")
            ->orderByRaw("LEFT(last_name, 1) $direction"),
        'id' => 'examples.id',
    ])
    ->paginate();

This can also be written using a long-hand closure if you need to do something more complex:

$examples = Example
    ::sortable([
        'initials' => function(Builder $query, $direction = 'asc') {
            $query
                ->orderByRaw("LEFT(first_name, 1) $direction")
                ->orderByRaw("LEFT(last_name, 1) $direction");
        },
        'id' => 'examples.id',
    ])
    ->paginate();

The second parameter, $direction will either be asc, desc or not given (uses the default you specify) - so you don't need to sanitise it again.

In case you need to invert the direction for some columns, call Alberon\LaravelUtilities\Support\Sortable::inverse($direction).