C4::Utils::DataTables - Utility subs for building query when DataTables source is AJAX
use CGI;
use C4::Context;
use C4::Utils::DataTables;
my $input = new CGI;
my $vars = $input->Vars;
my $query = qq{
SELECT surname, firstname
FROM borrowers
WHERE borrowernumber = ?
};
my ($having, $having_params) = dt_build_having($vars);
$query .= $having;
$query .= dt_build_orderby($vars);
$query .= " LIMIT ?,? ";
my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare($query);
$sth->execute(
$vars->{'borrowernumber'},
@$having_params,
$vars->{'iDisplayStart'},
$vars->{'iDisplayLength'}
);
...
This module provide two utility functions to build a part of the SQL query,
depending on DataTables parameters.
One function build the 'ORDER BY' part, and the other the 'HAVING' part.
my $orderby = dt_build_orderby($dt_param);
This function takes a reference to a hash containing DataTables parameters
and build the corresponding 'ORDER BY' clause.
This hash must contains the following keys:
iSortCol_N, where N is a number from 0 to the number of columns to sort on minus 1
sSortDir_N is the sorting order ('asc' or 'desc) for the corresponding column
mDataProp_N is a mapping between the column index, and the name of a SQL field
my ($having, $having_params) = dt_build_having($dt_params)
This function takes a reference to a hash containing DataTables parameters
and build the corresponding 'HAVING' clause.
This hash must contains the following keys:
sSearch is the text entered in the global filter
iColumns is the number of columns
bSearchable_N is a boolean value that is true if the column is searchable
mDataProp_N is a mapping between the column index, and the name of a SQL field
sSearch_N is the text entered in individual filter for column N
my %dtparam = = dt_get_params( $input )
This function takes a reference to a new CGI object.
It prepares a hash containing Datatable parameters.
my ( $query, $params )= dt_build_query_simple( $value, $field )
This function takes a value and a field (table.field).
It returns (undef, []) if not $value.
Else, returns a SQL where string and an arrayref containing parameters
for the execute method of the statement.
my ( $query, $params )= dt_build_query_dates( $datefrom, $dateto, $field)
This function takes a datefrom, dateto and a field (table.field).
It returns (undef, []) if not $value.
Else, returns a SQL where string and an arrayref containing parameters
for the execute method of the statement.
my ( $query, $filter ) = dt_build_query( $type, @params )
This function takes a value and a list of parameters.
It calls dt_build_query_dates or dt_build_query_simple function of $type.
$type can contain 'simple' or 'range_dates'.
if $type is not matched it returns undef