The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
DataTables-0.08
River stage zero No dependents
1 ++
/ DataTables

NAME

DataTables - a server-side solution for the jQuery DataTables plugin

SYNOPSIS

  use DBI;
  use DataTables;
  my $dbh = DBI->connect('DBI:mysql:databasename:localhost:3306', 'username', 'password') or die("Could not connect to database: $DBI::errstr");
  my $dt = DataTables->new(dbh => $dbh);

  #set table to select from
  $dt->tables(["dinosaurs"]);

  #set columns to select in same order as order of columns on page
  $dt->columns(["height","size","lovability"]);                  

  #print json back to browser
  $dt->print_json;                                               

  #if you wish to do something with the json yourself
  my $json = $dt->json;                                        

  # EXAMPLE WITH JOINS

  # Assume the following two tables:
  
  ----------        ------------
  |  pets  |        |  owners  |
  ----------        ------------
   id               id
   name             name
   owner_id         

  # Now we will join the tables on owners.id=pets.owner_id

  # the first key is a number because
  # order must be kept to match column order
  my %columns = (
                   0=>{"name"=>"owners"},
                   1=>{"name"=>"pets", AS=>"pet_name"}, # renaming isn't necessary here, unless you wish to use patterns
                );

  $dt->columns(\%columns);

  $dt->join_clause("owners.id=pets.owner_id");

  $dt->print_json;

  # Assume in the example above we know that all pets love scooby snacks, and we'd like to represent
  # that in our output. We can do that like so:
  my %patterns = ( 
                      "pet_name"=>"[% pet_name %] loves scooby snacks!", 
                 );
                 # notice if we didn't rename pets.name as "pet_name" in the example above
                 # and we had used name for both owners and pets, both
                 # columns would receive this pattern

  $dt->patterns(\%patterns);

  $dt->print_json;

  # A more realistic example might be putting a '$' before a money value,
  # but personally I find the scooby snacks example more useful

  # NOTE: Any getter/setter method can be set initially when creating the DataTables object can be passed into new()

DESCRIPTION

This module is an easy way to integrate server-side with the jQuery DataTables plugin. Currently this module is designed to work with legacy DataTables 1.9 and lower, and with DataTables 1.10. It supports basic features like displaying columns from a single table, but also supports more advanced features such as:

  1. Selecting columns from multiple tables via join

  2. Formatting output of returned columns

  3. Adding extra conditions to the where and join clauses

  4. Uses DBI place holders to help prevent SQL injection

METHODS

new(...)

Creates and returns a new DataTables object.

    my $dt = DataTables->new();

DataTables has options that allow you to receive your data exactly as you want it. Any of the method names below can be used in your new declaration to help initialize your object.

Here is an explicit list of all of the options and their defaults:

    tables  => undef,
    columns   => undef,
    dbh  => undef,
    patterns  => {},
    join_clause  => '',
    where_clause  => '',

tables

    $dt->tables(["table1","table2"]);

This method allows you to set which tables you want to select from in your SQL query. However, if you set "columns" to a hashref and not an arrayref, there is no need to set "tables".

columns

Columns can take in an arrayref or a hashref.

Arrayref:

    $dt->columns(["column1","column2"]);

OR if you are joining among multiple tables:

    $dt->columns(["table1.column1","table2.column2"]);

Keep in mind that if you use the patterns feature of this module, the column name given as the key of the pattern must match what you call the column here. So if you put "table1.column1", you must identify the pattern with that name. Also, you should list the columns in the order that they are on your page.

Hashref:

    my %columns = (
                        0=>{"column1"=>"table1"},
                        1=>{"column2"=>"table1"},
                        2=>{"column3"=>"table2", AS=>"new_col"},
                    );
    $dt->columns(\%columns);

Here the numbers are necessary because hashes don't keep order, so you must use numbers in order to specify the ordering of the columns on the page. The AS key allows you to specify what you would like to call that column (this relates to the SQL "AS" feature). This could be useful if you wanted to use the patterns feature of this module and had two columns in different tables named the same thing, because then they would both get the pattern! Also, if you provide a hashref for "columns", there is no need to supply the tables; DataTables will figure that out for you.

patterns

    $dt->patterns({"column1"=>"[% column1 %] rocks!"});

This method sets the patterns that you want to use for particular columns. You identify the pattern by using the column as a key, and then specify where in your pattern you would like the value to go by placing the name of the column between "[% %]". The name of the column must be the name that you specified in "columns". If you used a hashref in columns and specified the "AS" key, then you must use the value for that "AS" key.

data_output_format

The "json" in DataTables method returns an array of data source objects, one for each row, which will be used by DataTables. This can be an array of values, or a key-value-hash as a JSON object.

The data_output_format parameter defines wheter to output an array or a hash.

The default is column-id and returns:

    {
        "data":[
            [1,"Gecko","Firefox 1.0","Win 98+ / OSX.2+","1.7","A"]
        ],
        "draw":1,
        "recordsTotal":57,
        "recordsFiltered":57
    }

If you (optionally) define the column parameter for your DataTable and use the output format column-id, then it has to have the column indices as names:

    columns: [
        { data: "0" },
        { data: "1" },
        { data: "2" },
        { data: "3" },
        { data: "4" },
        { data: "5" }
    ]

Setting data_output_format to key-value will return:

    {
        "data":[
            {"platform":"Win 98+ / OSX.2+","browser":"Firefox 1.0","id":1,"engine":"Gecko","version":"1.7","grade":"A"},
            {"browser":"Firefox 1.5","platform":"Win 98+ / OSX.2+","id":2,"engine":"Gecko","grade":"A","version":"1.8"},
        ],
        "draw":1,
        "recordsFiltered":57,
        "recordsTotal":57
    }

Settings data_output_format to key-value will allow a DataTables column definition as follows:

    columns: [
        { data: "id" },
        { data: "engine" },
        { data: "browser" },
        { data: "platform" },
        { data: "version" },
        { data: "grade" }
    ]

Please note that in legacy DataTables 1.9, the column definition is set by aoColumns like this:

    $(document).ready(function() {
        var oTable = $('#example').dataTable( {
            "bProcessing": true,
            "bServerSide": true,
            "sAjaxSource": "sources/perlapp.cgi",
            "aoColumns": [
                { "mData": "id" },
                { "mData": "engine" },
                { "mData": "browser" },
                { "mData": "platform" },
                { "mData": "version" },
                { "mData": "grade" }
            ]
        } );
    } );

dbh

    $dt->dbh(DBI->connect(...));

Sets the database handle that should be used for the server-side requests.

join_clause

    $dt->join_clause("table1.id=table2.table1_id");

This lets you specify the condition that you want to join on if you are joining multiple tables. You can extend it with AND's and OR's if you wish.

where_clause

    $dt->where_clause("account_id=5");

This lets you specify extra conditions for the where clause, if you feel you need to specify more than what DataTables already does.

    $dt->print_json();

I recommend using this method to display the information back to the browser once you've set up the DataTables object. It not only prints the json out, but also takes care of printing the content-type header back to the browser.

json

    my $json = $dt->json();
    print "Content-type: application/json\n\n";
    print $json;

The json() method returns the json to you that the jQuery DataTables plugin is expecting. What I wrote above is essentially what the print_json() method does, so I suggest that you just use that.

REQUIRES

1 DBI
2 JSON::XS
3 CGI::Simple
4 SQL::Abstract::Limit
5 JQuery::DataTables::Request

EXPORT

This module has no exportable functions.

ERRORS

If there is an error, it will not be reported client side. You will have to check your web server logs to see what went wrong.

SEE ALSO

DataTables jQuery Plugin

JQuery::DataTables::Request, a library for handling DataTables request parameters.

AUTHOR

Adam Hopkins <srchulo@cpan.org>

COPYRIGHT AND LICENSE

Copyright (C) 2012 by Adam Hopkins

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.8.8 or, at your option, any later version of Perl 5 you may have available.