public
Created

  • Download Gist
datatables.pl
Perl
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72
#!/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;

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.