Laravel column sorting made easy

This is my shot at universal and easy to use model sorting in Laravel. The end result allows you to sort an Eloquent model using any column by clicking the column name. Everything is done in PHP, no JS involved.

Laravel/PHP Prerequirements

Traits are only supported in PHP 5.4+

Tested with Laravel 4.0, 4.1 and 4.2

Your routes must be named as theย link_to_sorting_action uses the current route name to generate the sorting links. If you use Resource routes they are named for you. If you have custom any routes, you can name them like this:

Route::get('questions-untranslated', array('as' => 'admin.questions.untranslated', 'uses' => 'QuestionsController@all'));

If you’re only using the standard Resource routes, you don’t need to modify your routing at all.

Add the Trait

What makes all of this possible are PHP Traits. Save this code as SortableTrait.php under app/Commands. You may need to create the folder.

<?php
trait SortableTrait {
    public function scopeSortable($query) {
        if(Input::has('s') && Input::has('o'))
            return $query->orderBy(Input::get('s'), Input::get('o'));
        else
            return $query;
    }

    public static function link_to_sorting_action($col, $title = null) {
        if (is_null($title)) {
            $title = str_replace('_', ' ', $col);
            $title = ucfirst($title);
        }

        $indicator = (Input::get('s') == $col ? (Input::get('o') === 'asc' ? '&uarr;' : '&darr;') : null);
        $parameters = array_merge(Input::get(), array('s' => $col, 'o' => (Input::get('o') === 'asc' ? 'desc' : 'asc')));

        return link_to_route(Route::currentRouteName(), "$title $indicator", $parameters);
    }
}

Modify the model

Addย use SortableTrait; to your model.

class User extends Eloquent {
    use SortableTrait;

    ...
}

Modify the controller

Change the index function of each models controller you wish to make sortable to include the sortable scope.

public function index() {
    $users = User::sortable()->get();
    return View::make('users.index', compact('users'));
}

Basically you just change

$users = User::all():

to

$users = User::sortable()->get();

Also works with pagination and other scopes

$users = User::female()->sortable()->paginate(10);

Modify the index view

Change your table headers to use the link_to_sorting_action function like this:

{{ SortableTrait::link_to_sorting_action('question') }}{{ SortableTrait::link_to_sorting_action('correct') }}{{ SortableTrait::link_to_sorting_action('created_at', 'Created') }}

The first argument is the Eloquent column name to sort by, and the optional second argument is the column title that is displayed to the user. If the optional column title is not provided, one will be generated using the Eloquent column name by replacing underscores with spaces and changing the first character to uppercase.

Pagination support

Change the pagination link in your views like this:

{{ $users->appends(Input::except('page'))->links() }}

License

The above code is licensed under the MIT license.

34 thoughts on “Laravel column sorting made easy

  1. This is a decent solution, however you could easily crash the application by modifying the input variables inside the URL to a non-matching column in your table giving a ‘Column not found’ exception. You need to do some validation before hand to make sure the columns exist in your database table, probably using the built in Laravel function ‘Schema::getColumnListing(‘users’);’

  2. I tried to implement this and get an “undefined method IlluminateDatabaseQueryBuilder::sortable()” error. Is this no longer viable for Laravel 4.2?

  3. One other question. I tried updating your link_to_sorting_action to include a bootstrap glyphicon only to realize that link_to_route escapes html characters sent into its title. Any idea how I might be able to add a span into the link?

    • You could make link_to_sorting_action just return the URL and build the actual link yourself. Try replacing link_to_route with route(Route::currentRouteName(), $parameters);

      • I actually did it this way:

        $link = link_to_route(Route::currentRouteName(), $title, $parameters);
        $formattedLink = preg_replace(‘//’, ‘ ‘.$indicator.’‘, $link);

  4. One last issue I swear. If you use this with pagination() it does not pass along the sort or orderby params on pagination click….looking into a fix

      • So I added this to the controller:

        $order = Input::get(‘o’);
        $sort = Input::get(‘s’);
        return View::make(‘admin.plotlines.index’, compact(‘plotlines’, ‘order’, ‘sort’));

        Then I added this to the view:

        {{ $plotlines->appends(array(‘s’ => $sort, ‘o’ => $order))->links() }}

        This works but I feel like the point of the trait was to pull that logic out of the controller…what do you think?

        • I would post a question on SO about how to handle the generic situation of wanting to include arbitrary data in the pagination. There are probably other nice ways of doing this that neither you or me know ๐Ÿ™‚

          • This answer WAS from SO :P. My only problem is that the trait pulls the input retrieval into itself….and with this solution I am then pulling it back into the controller. Now its in both places….feels dirty ๐Ÿ˜›

          • I solved it almost just like you:

            {{ $orders->appends(Input::except(‘page’))->links() }}

            Get’s all parameters from url to paging links.

        • I solved it almost just like you:

          {{ $orders->appends(Input::except(‘page’))->links() }}

          Get’s all parameters from url to paging links.

      • Also I had to update the $parameters variable to take into account the page so it didnt refresh to page 1 on each sort. This should also work if your not using pagination as the extra param will be ignored.

        $parameters = array_merge(Route::getCurrentRoute()->parameters(), array(‘s’ => $col, ‘o’ => (Input::get(‘o’) === ‘asc’ ? ‘desc’ : ‘asc’), ‘page’ => (Input::has(‘page’) ? Input::get(‘page’) : 1)));

  5. Somewhy my trait is not seen in a view. I get an error:
    Class ‘TraitsSortableTrait’ not found.

    And i didn’t find anything in PHP documentation that static method of trait can be called as if it were class’s static method. Like you use here:

    SortableTrait::link_to_sorting_action(‘question’)

    • Thats how PHP traits work, they add methods to Classes like Categories in Objective-C. You must have misplaced the SortableTrait.php file i think. Can you post the beginning of your Class and where you put SortableTrait.php?

      • I have this:
        <?php namespace Traits;

        trait SortableTrait {

        And i put it into /app/traits/ folder. But i also added this folder to composer json autoload classmap.

      • I have in SortableTrait:

        namespace Traits;

        trait SortableTrait {

        And i put it into /app/traits/ folder. But i also added this folder to composer json autoload classmap.

        • It works for me when i add it to one of my Laravel projects. I think your problem is fiddling with namespaces. Your error clearly says it cannot even find the class. I suggest you remove the namespace and try putting the file in Commands just to make sure its actually autoloaded properly.

      • So what i try to say that official PHP documentation sais:
        That you can call a Static Method From a Class That Uses a Trait.
        But i didn’t find anything about that you can call static method directly from trait like you did here:

        SortableTrait::link_to_sorting_action(‘question’)

        It should have been like that then:

        User::link_to_sorting_action(‘question’) – because User uses SortableTrait.

        So something is wrong here.

  6. I fixed that ‘Class not found’ error. It was yes problem of namespaces.

    I will add my 5 cents after all:

    1) I made a separate folder /app/traits. Because app/Commands folder is not an apropriate place for traits. In order to make traits work from /app/traits you need to add it to composer.json into autoload classmap, along with other folders like controllers and models. Then need to run composer dump-autoload. This was my problem, i had to run it with sudo. Without sudo somewhy it was not generating stuff correctly.

    2) I improved your concept a bit in term of parameters validation. And it meets standart Laravel model logic. I added to SortableTrait an abstract method
    public abstract function getSortable();

    Model that uses Sortable trait has:

    /** Sortable needed for sorted queries. Against this array is check run, if column name is not in array it will not work */
    public $sortable = array(‘id’, ‘name’, ‘city’);

    and implements an abstract method:
    public function getSortable()
    {
    return $this->sortable;
    }

    This is needed in scopeSortable($query) method of SortableTrait. There we check if passed value of ‘s’ parameter meets the required column names. You had an issue with that, if you pass bullshit to parameters it would fail.

    public function scopeSortable($query){
    if(Input::has(‘s’) && Input::has(‘o’)) {
    $sortBy = Input::get(‘s’);
    $order = Input::get(‘o’);
    if (in_array($sortBy, $this->getSortable()) && in_array($order, $this->asc_desc)) {
    $query->orderBy($sortBy, $order);
    }
    }
    return $query;
    }

    3) I extended Blade. In order in a view it would look like not like yours:
    {{ SortableTrait::link_to_sorting_action(‘question’) }}

    But i have it like blade expression:
    @sortingUrl(‘name’)
    or
    @sortingUrl(‘name’,’Name Title’)

    For that i created blade_extension.php in /app/start folder. And did that:

    Blade::extend(function($view, $compiler) {
    $pattern = $compiler->createMatcher(‘sortingUrl’);

    $replace = ‘[?php // replace [ with angle bracket. This blog strips it out.
    echo SortableTrait::link_to_sorting_action(array$2);
    ?]’; // replace ] with angle bracket. This blog strips it out.
    return preg_replace($pattern, $replace, $view);
    });

    And at the end of the global.php i added this line:
    require app_path().’/start/blade_extensions.php’;

    4) And this is how looks mine function link_to_sorting_action.

    public static function link_to_sorting_action($params){
    if(count($params)==1){
    $params[1] = ucfirst($params[0]);
    }
    $column = $params[0];
    $title = $params[1];

    if (Input::get(‘s’) == $column){
    if(Input::get(‘o’)===’asc’){
    $parameters = array_merge(Route::getCurrentRoute()->parameters(), array(‘s’ => $column, ‘o’ => (‘desc’)));
    $icon = “ “;
    } else {
    $parameters = array_merge(Route::getCurrentRoute()->parameters(), array(‘s’ => $column, ‘o’ => (‘asc’)));
    $icon = “ “;
    }
    } else {
    $parameters = array_merge(Route::getCurrentRoute()->parameters(), array(‘s’ => $column, ‘o’ => (‘asc’)));
    $icon = “ “;
    }
    return $icon.link_to_route(Route::currentRouteName(), $title, $parameters);
    }

    Conclusions.
    I am not a PHP developer and didn’t know about Traits. So i implemented same model myself almost exactly as you, but by extending from BaseModel and bla bla bla. But when i saw your solution with Traits, i really liked it. So i rewrited it. And changed a bit.
    * I added columns/params validation mechanism.
    * And my blade extension allows me to make more clear templates, just by using blade expression:
    @sortingUrl(‘name’)
    Which is cool.

    Now you can throw stones at me.

  7. hello

    i have a question: what if you have two tables that you join with JOIN statement , how can use sortable() on the result?

    instead of User table :
    $users = User::sortable()->get();

    i need two tables , say users with userBooks table.

      • What I have is books table and user_book table and users table. My result is a table that shows all the books per authenticated user. pretty simple.
        It works but trying to implement sortable() is failing.

        I can’t find the way to execute my sql query using model
        of my tables . It just doesn’t work write with join statement.
        so the only way it works is like that:

        $books = DB::select(‘select books.id, books.author, books.title, books.is_public, user_book.rank, user_book.comments from books join user_book on books.id = user_book.book_id where user_book.user_id = ?’, array(Auth::id()));

        with this example sortable() doesn’t work.
        how can i rewrite that so sortable() would work?
        thanks in advance

        • Ok, you are coming at the problem from the wrong direction ๐Ÿ™‚ You should use Eloquent relations instead of doing a raw DB::Select. This is the power of Laravel, and using DB::Select should be reserved to very special edge cases that Eloquent cannot handle. You may need to change your database model to make it work, and i highly recommend that you do this change in that case. Basically you create a pivot table and then add a function like this to your User model:

          public function books() {
          return $this->hasMany(‘Book’);
          }

          And this to your Book model:

          public function user() {
          return $this->belongsTo(‘Book’);
          }

          Then you can get all your users sorted like this:
          $users = User::sortable()->get()
          And then foreach user you can get their books in the Blade view like this:
          $user->books()->get()->all()

          Read more:
          http://laravel.com/docs/4.2/eloquent#many-to-many

          • first of all thank you for the answer, it was helpful, however
            I don’t know why but i get an error:

            Call to undefined method IlluminateDatabaseEloquentCollection::books()

            and also i need to pull data from 3 tables and not 2 , my book fields consist of columns from 2 tables (books, user_book) and only then user table for association with certain user.
            I also have userBook model of course…
            I am a bit stuck…;(

Leave a Reply

Your email address will not be published. Required fields are marked *