(I offered to write an entry for the Catalyst Advent Calendar based on a private wiki article I use, and wanted to break the 600+ day blogging dry spell!)

Web user interfaces have come a long way since the original publish/view model of the Internet – these days, people expect interactivity and interfaces that let them get the information they want with the minimum of effort. JavaScript libraries such as jQuery have made it easier to develop cross-platform, interactive interfaces, but tables of data have still lagged behind a bit. There is a jQuery plugin that adds interactivity to tables of data in HTML – datatables.net. The DataTables plugin allows for server-side processing of table options such as ordering, filtering and pagination, leading to much smoother feeling data tables.

Assumptions

I’m going to assume you already have a Catalyst application set up (or know how to set one up), have already set up your DBIC classes, and know how to load jQuery and the DataTable plugin on your page – all of which is documented on the relevant projects’ websites. In short, I’m going to assume you’re just here to add an interactive data table to your page.

Building the datatable

The process is split into three parts – some HTML to add to the page (for the data table), some JavaScript to be added to the page (to set up the data table), and some Perl code (the get the data for the data table). For the most part, these blocks can simply be dropped in as they are, with only minimal changes required to get it working – there are plenty of optimisations possible, and have been left as an exercise for the reader.

The HTML

This is pretty straightforward – it’s just a normal HTML table, with a DOM ID that we’ll use to reference it in the JavaScript:

<table id='datatable' style='width: 100%'>
  <thead>
    <tr>
      <th>Woo</th>
      <th>Yay</th>
      <th>Houpla</th>
    </tr>
  </thead>
  <tbody>
  </tbody>
</table>
<p>
  <label for='filter_woo'>Search 'woo' for items starting with : </label>
  <input type='text' id='filter_woo' />
</p>

It’s a pretty simple table – three columns, with no data pre-loaded. The column headers aren’t significant – they don’t refer to database field names, so make them user friendly.

I’ve added a simple text input field to the page – it’s not required, but I’ll be using it as an example of filtering.

The JavaScript

The JavaScript also holds very few surprises:

$(document).ready(function() {
    oTable = $('#datatable').dataTable( {
        "bProcessing": true,  // Indicate when the table is processing data
        "bServerSide": true,  // Indicate that the datatable gets data from a
                              // HTTP GET request
        "sDom" : "lrtip",     // Remove the global filter field, as it's horrible
        "sAjaxSource": "/path/to/action"  // The actual URL to call for data
    } );

    /* Add a click handler to the rows */
    $("#datatable tbody").click(function(event) {
        // Clear selection from any existing rows
        $(oTable.fnSettings().aoData).each(function (){
            $(this.nTr).removeClass('row_selected');
        });

        // Set the class for the selected row
        $(event.target.parentNode).addClass('row_selected');

    });

    // Add a callback to a text <input> so that it can automatically search
    // rather than require a submit button
    $('#filter_woo').bind("input", filter_woo );
} );

// Add a filter for a specific column
function filter_woo() {
    var t = $('#datatable').dataTable();

    // In this case, only search if the <input> value is long enough (stops
    // stupid searches on single chars)
    if ( $('#filter_woo').val().length > 3 ) {
        t.fnFilter($('#filter_woo').val(),0);
    }
    else {
        t.fnFilter('',0);
    };
}

The ‘sAjaxSource’ value will need to be replaced with the path to the Catalyst action, which we’ll create in the next section.

There are two ‘advanced’ things in here – first of all, there’s a listener added to the datatable to handle when the user clicks on a row (it adds a new class to that row, allowing it to be easily found by JavaScript later), and a listener on the filter text box we created in the HTML to automatically update the datatable. Neither of these are required, but I’ve included them because it’s likely you’ll want to do this anyway.

The text input filter function filter_woo() contains the first surprise – a call to a datatable method fnFilter(). This takes two parameters – a value, and a column index (counting from the left, starting at 0). The value is context free – you can put whatever you like in there, as it’s going to be handled in the Perl code. This means you can get creative when building your filtering options – checkbox groups, drop-down menus, progressive search fields, as many and as varied as you like, provided there is only a single filter value for each column. Making the call to fnFilter() immediately causes an AJAX request for data, passing all of the filter options – the state for them is kept by the datatable, so you’ll need to clear the filter by passing an empty value to the columns you don’t want to filter on.

The Perl

Finally, we need some Perl to get the data and return it to the datatable.

The datatable is going to expect a JSON block back, so the first thing to do is create a JSON view. Thankfully, this is utterly trivial with Catalyst, just by running:

./script/myapp_create.pl View JSON JSON

There – one JSON View, quickly and easily created. Personally, I add the following configuration to limit what parts of the stash actually gets sent through the JSON View:

<View::JSON>
  expose_stash = json
</View::JSON>

I prefer to put anything to be serialised to JSON under $c->stash->{json} – this can stop information in the stash being accidentally leaked via JSON.

Now, we just need a Catalyst Action:

sub datatable : Local Args(0) {
    my ($self, $c) = @_;

    # We need to map columns to field names for ordering, so may as well define
    # them here
    my @columns = qw/col_name_1 col_name_2 col_name_3/;

    my $filter;
    # This will all depend on what you've done for filtering options in the HTML,
    # but you'll be turning the values you passed in with fnFilter() into standard
    # DBIC filters
    if ( $c->request->param('sSearch_0') ) {
        $filter{col_name_1} = { 'LIKE', sprintf('%s%%', $c->request->param('sSearch_0') ) };
    };

    # Sorting options
    my @sorting;
    my $i = -1;
    while ( $i++ < $c->request->param('iSortingCols') ) {
        push @sorting, sprintf('%s %s', $columns[$c->request->param("iSortCol_$i")], $c->request->param("sSortDir_$i") );
    }   

    # May need editing with a filter if the table contains records for other items
    # not caught by the filter e.g. a "item" table with a FK to a "notes" table -
    # in this case, we'd only want the count of notes affecting the specific item,
    # not *all* items
    my $total_records = $c->model('Database')->resultset('Table')->count;

    # In case of pagination, we need to know how many records match in total
    my $count = $c->model('Database')->resultset('Table')->count( $filter );

    # Do the search, including any required sorting and pagination.
    my @search = $c->model('Database')->resultset('Table')->search(
        $filter,
        {
        columns => \@columns,
        order_by => \@sorting,
        rows => $c->request->param('iDisplayLength'),
        offset => $c->request->param('iDisplayStart'),
        }
    );

    # View::JSON doesn't like objects, so let's flatten them here. Can also be used
    # to turn (1|0) values into ("Yes"|"No")
    my @results;
    map {
        push @results, [
            $_->col_name_1,
            $_->col_name_2,
            ( $_->col_name_3 ? 'Yes' : 'No' ),
        ];
    } @search;

    $c->stash({
        json => { # The supposes you configured View::JSON with "expose_stash=json"
            'sEcho' => $c->request->param('sEcho'),
            'iTotalRecords' => $total_records,
            'iTotalDisplayRecords' => $count,
            'aaData' => \@results,
        },
    });
    $c->forward( $c->view('JSON') );
}

I’ve rather unimaginately called it ‘datatable’, but you can call it whatever you like – you’ll need to plug the URL for the action into the ‘sAjaxSource’ parameter in the JavaScript. I’ve also continued the confusing naming scheme by having a Model called ‘Database’ for the database, and selecting from a table called ‘Table’.

Running the application in debug mode is recommended for getting the filters working – you’ll be able to see exactly what’s being passed in. In short, the fnFilter( $value, $column ) maps to CGI parameters sSearch_$column => $value.

The result

You should find that, barring the small edits needed to create any filters you want and to set the right URLs and DBIC classes, your datatable should be working properly – selecting a different number of entries to display, changing column sorting order, paging backwards and forwards, and changing filters should all result in AJAX calls to the Catalyst application for data. All three blocks of code are pretty portable – I copy and paste them out of a wiki page whenever I need them.