Mojolicious response to http://jmorano.moretrix.com/2013/10/datatables-perl-and-bit-jquery/

datatables.pl

#!/usr/bin/env perl

use Mojolicious::Lite;
use DBIx::Connector;
use SQL::Abstract;

helper db => sub { 
  state $db = DBIx::Connector->connect("dbi:mysql:host=localhost;db=testdb", 'testuser', 'xxxsecret')
};

# Get the total count of rows in the table
helper get_count => sub {
  my $db = shift->db;
  my $sql_count = "select count(id) from geo_data";
  return $db->selectrow_arrayref($sql_count)->[0];
};
 
# controller independent query builder
helper build_query => sub {
  my ($c, $search, $order, $limit, $offset) = @_;

  my @columns = qw/ip country_name city latitude longitude/;
  my @where = map { +{$_ => {'-like' => $search}} } ( $search ? @columns : () );

  my $sql = SQL::Abstract->new;
  my ($stmt, @bind) = $sql->select('geo_data', \@columns, \@where, $order || []);

  # add limit and offset, for which I do not know the SQL::Abstract way
  $stmt .= ' LIMIT ? OFFSET ?';
  push @bind, $limit || 25, $offset || 0;
  return $stmt, \@bind;
};

# controller side query interface
helper query => sub {
  my $c = shift;
  return $c->build_query(
    $c->param('sSearch'), 
    $c->munge_order_params,
    $c->param('iDisplayLength'),
    $c->param('iDisplayStart'),
  );
};

helper munge_order_params => sub {
  my $c = shift;
  my $num_sort_cols = $c->param('iSortingCols') || 0;
  my @order = 
    map {
      my $col = $c->param("iSortCol_$_");
      my $dir = $c->param("iSortDir_$_");
      { -lc($dir) => $col };
    }
    (0 .. $num_sort_cols-1);
  return \@order;
};

any '/' => sub {
  my $c = shift;
  my ($stmt, $bind) = $c->query;
  my $data = $c->db->selectall_arrayref($stmt, {Slice =>[]}, @$bind);
  my $echo = $c->param('sEcho');
  my $count = $c->get_count;
  $c->render( json => {
    aaData => $data, 
    iTotalRecords => $count, 
    iTotalDisplayRecords => $count, 
    sEcho => int($echo),
  });
};

app->start;