Tag: Database

If you follow Railscast 228 you get sortable table columns for your model. But what if you don’t want to expose the name of the actual database columns or more interesting if you want to sort across tables? Here is how I do it.

In your controller add order or reorder if you already have an order clause in one of the used scopes (default_scope counts too).

class AttendeesController < ApplicationController
  def index
    @attendances = @conference.attendances.reorder(sort_query).page(params[:page])
  end

  # ...
end

As I’m using this mechanism in different controllers I added the common functionality to the application_controller.rb file.

class ApplicationController < ActionController::Base
  helper_method :navigation_params, :sort_column, :sort_direction

  protected

  # supports only attendances for now
  ALLOWED_SORT_COLUMNS = {
    "fee_payed" => "fee_payed",
    "fee_payed_on" => "fee_payed_on",
    "name" => "users.last_name, users.first_name",
    "payment_confirmation_sent_at" => "payment_confirmation_sent_at",
    "registered_at" => "registered_at",
    "registration_confirmation_sent_at" => "registration_confirmation_sent_at",
    "town" => "users.town"
  }

  # use this in views
  def sort_column
    ALLOWED_SORT_COLUMNS.keys.include?(params[:sort]) ? params[:sort] : "name"
  end

  def sort_direction
    %w[asc desc].include?(params[:direction]) ? params[:direction] : "asc"
  end

  def navigation_params
    { direction: params[:direction], page: params[:page],  sort: params[:sort] }
  end

  def sort_query
    sort_query_column.split(',').map{ |column| column + ' ' + sort_direction }.join(', ')
  end

  # use this in controllers
  def sort_query_column
    ALLOWED_SORT_COLUMNS[sort_column]
  end
end

This will use the ALLOWED_SORT_COLUMNS hash to map between user visible and actual database sort columns. Adding sort_query also allows us to sort by multiple columns at once. navigation_params is a shortcut I use when generating URLs (e.g. in link_to) and I want to preserve pagination, sorting, filters/searches, etc. across pages.

  def link_to_sortable(column, title = nil)
    title ||= column.titleize
    sort_icon = column == sort_column ? content_tag(:i, nil, class: (sort_direction == "asc" ? "icon-chevron-down" : "icon-chevron-up")) : ""
    direction = column == sort_column && sort_direction == "asc" ? "desc" : "asc"
    link_to (title+" "+sort_icon).html_safe, params.merge(sort: column, direction: direction, page: nil)
  end

Note that sort_icon assumes you are using Bootstrap.

Now we can have sortable columns in our views:

<%= link_to_sortable "name" %>
...