SortableSkima

Description

This works in rails 3 before asset pipeline. Haven't tested with anything else.

This gem aids in building sortable and filtrable tables with remote sources. This was done for our internal applications and has been very usefull for us it saves a lot of code and time when making sortable tables. It takes a non standart approach by passing the ruby and SQL to be executed in strings wich are then run with eval. Don't worry though no code is sent or received to the frontend.

This is the first public gem I've released. If you'd like to use it and encounter some issues please let me know so I get some motivation to improve it. Knowing someone is using it would be great!

Table contents are loaded as JSON by AJAX and built by javascript. You can listen for the 'onSortableLoaded' JS event and use the data for other purposes. You can also reproduce the resulting query in the server if needed.

Jquery-ui is being used to build the table headers and you'll likely have to add them to your app. JS files are being added to rails defaults on initialization wich is likely not suitable for most people and I imagine will cause errors with rails 3.1.9 or newer, I itend to fix this in a near future.

Installation

Add this line to your application's Gemfile:

   gem "sortable_skima"

Run:

    $ bundle

Create both backtraces and sortables migrations if you dont have them already in your db

    create_table :sortables do |t|
        t.text :query
        t.timestamps
    end
    create_table :backtraces do |t|
        t.integer  :user_id
        t.string   :action
        t.string   :value
        t.timestamps
    end

Tables

Example usage

    <%= sortable_table_tag 'Project.select("projects.*, COUNT(warnings.id) as warnings_count").joins("LEFT JOIN users ON users.id = projects.manager_id '+
						'LEFT JOIN divisions ON divisions.id = projects.division_id '+
						'LEFT JOIN budgets ON budgets.project_id = projects.id AND budgets.active '+
                        'LEFT JOIN budget_stats ON budget_stats.budget_id = budgets.id '+
						'LEFT JOIN project_types ON project_types.id = projects.project_type_id '+
                        'LEFT JOIN warnings ON warnings.warnable_type = \'Project\' AND warnings.warnable_id = projects.id")'+
						'.joins( :project_stats).group("projects.id")',[
                   {:sort_field=>'projects.id',:title=>'Name',:display_method=>"summary",:path_method=>"project_path(model)",:td_class=>'align_center', :style=>'width: 200px;'},
                   {:sort_field=>'manager_id',:title=>'Manager',:display_method=>"manager.nil? ? '---' : manager.username",:path_method=>"model.manager.nil? ? nil : user_path(model.manager)",:td_class=>'align_center', :style=>'width: 70px;'},
                   {:sort_field=>'divisions.name',:title=>'Unit',:display_method=>'division.name rescue "---"',:td_class=>'align_center',:style=>'width: 100px;'},
                   {:sort_field=>'nature',:title=>'Nature',:display_method=>'nature',:td_class=>'align_center',:style=>'width: 70px;'},
                   {:sort_field=>'project_types.name',:title=>'Type',:display_method=>'project_type.name',:td_class=>'align_center',:style=>'width: 70px;'},
                   {:sort_field=>'status',:title=>'Status',:display_method=>'get_status',:td_class=>'align_center',:style=>'width: 70px;'},
                   {:sort_field=>'warnings_count',:title=>'Alarms',:display_method=>"warnings_count",:td_class=>'align_center',:style=>'width: 70px;'},
                   {:sort_field=>'price',:title=>'Price',:display_method=>"price",:td_class=>'align_center',:style=>'width: 70px;'},
                   {:sort_field=>'budget_stats.estimated_costs',:title=>'Est. Cost',:display_method=>"active_budget.budget_stats.estimated_costs rescue '---'",:td_class=>'align_center',:style=>'width: 70px;'},
                   {:sort_field=>'project_stats.actual_cost',:title=>'Actual Cost',:display_method=>"project_stats.actual_cost",:style=>'width: 90px;'},
                   {:sort_field=>'progress',:title=>'<small>Prog<sup>3</sup></small>',:display_method=>"100 * project_stats.progress.round(2) if !project_stats.progress.nil?",:style=>'width: 70px;'}
           ],:items_per_page=>12,
           :class=>'fixed_columns alternatingRows',
           :style => "padding-top:10px; padding-bottom:10px;" %>

This will render a sortable table. with the given settings.

Method sortable_table_tag( base_query, columns=[], opts={} )

-Arguments -base_query A string containing a set of rails commands that must return an ActiveRecord::Relation.

    The query must contain a reference to any tables that will be used in either sorting or filtering either through a join() or an include().

    NOTICE: a simple 'join()' will result in an INNER JOIN exluding any unpaired results.
-columns
    An array of hashes representing each column in the table

    The arguments for each column are:
    [:sort_field]
        The SQL field wich will be used for sorting this will be placed in the DB query
    [:title]
        The title for the html table header, can contain html, if blank the sort_field will be used
    [:display_method]
        The method to generate the content of each cell in the column, this is run in each model instance, and has controller_context and view_context available if needed
    [:path_method]
        The method used to generate an url which is then used to convert the cell into an hyperlink, run in the controller context with the variable 'model' available containing the model instance for each row
    [:no_display]
        This field wont be parsed to html.

        Useful for when extra data is needed for JS or to pass specific CSS for each row.

        NOTICE: Possible bug if the no_display columns are not added after displayed columns, untested.
    [:disabled]
        Boolean. Disables ordering on this column.
-options
    [:default_order]
        Order to be used in case there is no order selected.
    [:items_per_page]
        For paginate. Defaults to 10.
    [:disable_backtrace]
        Boolean. Disables caching of the selected order and filters. Unstable.
    [:paginate]
        Boolean, default true. Enable pagination.
    [:group]
        String. ID of the table and filters group. For when there is more than one sortable in the same page.
    [OTHER OPTIONS]
         All options accepted for a 'content_tag' helper. EX: :class, :style , :some_attribute

Filters

Example

    Unit: <%= sortable_filter_tag  "division_id", ['All']+Division.all.map{|x|[ x.name, x.id]}, :style => "width:90px"%>
    Status: <%= sortable_filter_tag  "status", ['All']+Project::STATUSES, :style => "width:90px"%>
    Nature: <%= sortable_filter_tag "nature", ['All']+Project::TYPES, :style => "width:90px;"%>
    Type: <%= sortable_filter_tag "project_type_id", ['All']+ProjectType.all.map{|x|[ x.name, x.id]}, :style => "width:90px"%>
    Manager: <%=sortable_filter_tag("manager_id", users, :style => "width:90px") %>

Method sortable_filter_tag(column_name, options_list, opts = {})

[Arguments] [column_name] String with the SQL column to be used for filtering. (Discarded if :raw_sql is true) [options_list] An array with 1 or 2 dimensions .

    The first element ( ar[i][0] ) is displayed the second ( ar[i][1] ) is the actual value to be matched. ( some_query.where(column_name => option_value) )

    If it has only one dimension both values are the same.

    If :raw_sql is true then the second element should contain a valid WHERE condition. EX: 'users.login_attempts > 5'
[opts]
    [:raw_sql]
        Boolean. Defaults to false.

        If true the value in the options list will be used literaly in the query ( some_query.where(option_value) )
    [:group]
        ID to match the one in the sortable table that is related to this filter. For when there is more than one sortable in the same page.
    [:selected]
        The selected option. Must be the same as the option value.
    [OTHER OPTIONS]
        All options that are valid for a 'select_tag' helper

Frontend

This gem has Javascript and CSS files included to make the tables work on the browser

*These files are added automatically to config.default_js *

These files must be present in your views for the frontend to work

skima-sortable-tables.js ,jquery.ba.bbq.js ,jquery-ui.min.js ,jquery-ui.css

-Events -sortable.loaded Fired after the table data has been loaded and rendered

    Arguments passed with event

    data: The data received for the table

    $table: The table element Jquery reference

    queryID: Unique identifier for the sortable query on the server

Re-using sortable filters and orders

Catch the data loaded event in javascript

    $('your sortable table or document or whatever').on('sortable.loaded',someFunction);
    function someFunction(evt, data, $tableElement, queryID){
        //Send the reference to the query to the server
        $.ajax({
            url:'some_path'
            data:{sortable_id:queryID}
        })
    }

Then use the sortable filters in your controller

    def some_action
        my_base_query = User.where('some_rule')# or User or User.order_by() or SomeModel.join() or whatever
        include_order = false
        users = SortableSkima.attach_sortables( my_base_query,  params['sortable_id'], cur_user_or_admin, include_order)

        #...do what you want with your results
    end

---

sortable_skima

Runtime