Skip to content

@jberger /datatables.pl
Created

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
#!/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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.