Converting jqGrid filtering and sorting options to MySQL statements using Perl. And processing them.

| May 1, 2017 | Updated: March 14, 2020

I think that jqGrid is a nice jQuery plugin for interfaces, where representation of data in a form of interactive tables is the most convenient approach.
If you are able to stay in the limits of default jqGrid behavior – it is fairly easy to use and there are plenty of basic examples.
If you need something special – things might become tricky, but anyway, there are many chances that your problem is already reported and solved on StackOverflow.

The only unsolved problem that I faced was significant slowness of jqGrid’s operations in Chrome browser. JqGrid works much faster in Firefox at the time, when I am writing this post an it was so two years ago. Maybe jqGrid had this problem in 2010 too, but I didn’t run Chrome at that time 🙂

Here I will describe a pattern that I used for a couple of years to pass filtering and sorting options from jqGrid to MySQL server.
Instance of jqGrid, which is used here, is simple. What I actually want to share are two Perl subroutines – get_searchstring() and _get_filterops that allow to translate almost any filtering request from jqGrid to MySQL.
I can’t guarantee anything, you know, but these subroutines worked well for several years and didn’t manifest any glitches. The only thing that should be done, I think, is a simple user input checking for invisible Unicode letters and so on.

You can see a fully functional example at the bottom of this page.

Consider a web page, containing jqGrid. It’s a plain html page. Backend is a perl CGI script.
Grid has a default sorting column and filter is also enabled.
Now, grid is sending request to CGI script requesting initial data and this request contains filtering and sorting options.
How should we process them?

Here is the pattern:

1)
I assume that CGI module is used to handle http request
DBI module is used for MySQL access
JSON::XS module is used to convert Perl’s hash to json for jqGrid
You also need:
POSIX for ceil()
HTML::Entities in order to read json.
All further information is about Perl cgi script.

# hash for response to JqGrid
my %resp=();
 
# sorting and pagination params from JqGrid
my $lines = $cgi->param('rows') // '';
my $page  = $cgi->param('page') // '';
my $sidx  = $cgi->param('sidx') // '';
my $sord  = ($cgi->param('sord') eq 'asc')?'asc':'desc';

2)
preparing a hash that represents binds between jqGrid column ids and MySQL column names:

1
2
3
4
5
6
7

my %tbl_names = (
	id		=> 'sql_id',
	jq_date		=> 'sql_date',
	jq_name 	=> 'sql_name',
	jq_value	=> 'sql_value',
	jq_weight	=> 'sql_weight',
);

in some cases it may be needed to add CAST to sql names, for example

jq_dir => 'CAST(sql_dir AS CHAR)'

it depends on your table structure

3)
create an SQL substring with filter data from jqGrid

my $srch_res = get_searchstring( $cgi, \%tbl_names, 'HAVING' );

This request must contain cgi object (with jqGrid request inside it), reference to hash from step 2 and a string.
A string may be one of ‘HAVING’, ‘WHERE’, ‘AND’ or empty.
What string should be used depends on the step 4 and the kind of request you will use there.
The string will be used as a wrapper for a search substring, so
if you have grouping or any computed fields in the rest of your request – you should use ‘HAVING’,
if your substring will be the one and only part of the WHERE clause – you can use ‘WHERE’ if there is no computed fields involved,
if your request already contains WHERE clause with any conditions and doesn’t contain grouping/computed fields – you can use ‘AND’.
if you wish you can use an empty string and write down wrapper directly in the main request

WHERE hardcoded_condition >0 HAVING(conditions_from_jqGrid)
WHERE(conditions_from_jqGrid)
WHERE hardcoded_condition >0 AND(conditions_from_jqGrid)
WHERE conditions_from_jqGrid

get_searchstring() returns a hashref in which
$$srch_res{srch_string} contains substring that will be interpolated into your request statement in step 4.
@{$$srch_res{binds}} contains actual filter values that will be passed to placeholders in step 6.

4)
Create a request appropriate for your grid and interpolate $srch_res at the end.

$statement = "  SELECT SQL_CALC_FOUND_ROWS 
                    sql_id, sql_date, sql_name, sql_value,   
                    ROUND( sql_value * 100/(sql_value + sql_id) ) AS sql_weight
                    FROM my_sql_table
                    LEFT JOIN my_another_sql_table ON (sql_date = sql_other_date)
                    $$srch_res{srch_string}"

5)
Add to your request statement sorting and pagination constrains

if ( $sord ne '' and  $sidx ne '' and $tbl_names{$sidx}){
    $statement .= " ORDER BY $tbl_names{$sidx} $sord";	
}
 
$statement .= " LIMIT ?, ?";

6)
At previous step we’ve gotten an SQL statement with placeholders.
Now we should perform this request using actual values.

If you have no additional constrains besides $srch_res your request will look something like this:

my $rows = $my_dbh->selectall_arrayref( $statement, undef,  @{$$srch_res{binds}}, ($page-1)*$lines, $lines);

Then you should perform one more request to acquire calculated size of the full resultset.

( $resp{records} ) = $my_dbh->selectrow_array("SELECT FOUND_ROWS()");

7)
And finally send all data to jqGrid in appropriate format

# total pages
$resp{total} = ceil($resp{records}/$lines);
# current page
$resp{page}= $page;
# forming hash and send it to jqGrid using JSON::XS
my $i=0;
foreach my $row (@$rows){
 
 $resp{rows}[$i++] = {
       id      => $$row[0],
       cell    => [
         $$row[1], 
         $$row[2] . "mg", 
         $$row[3] * 5,
         $$row[4],
      ]
   };
}
 
send_json_data( \%resp );

Two service routines that are the actual point of this post:

But they are too long 
######################################################
=pod
 
'eq'		'equal'
'ne'		'not equal'
'lt'		'less'
'le'		'less or equal'
'gt'		'greater'
'ge'		'greater or equal'
'bw'		'begins with'
'bn'		'does not begin with'
'in'		'is in'
'ni'		'is not in'
'ew'		'ends with'
'en'		'does not end with'
'cn'		'contains'
'nc'		'does not contain'
 
num_cast - to perform this comparison, operand must be casted to number
str_cast - to perform this comparison, operand must be casted to string
=cut
 
sub _get_filterops{
 
  my %filterops = (
	'eq' => {
	  str 		=>	sub{ '= ?' },
	  bind		=>	sub{ return shift }
	},
	'ne' => {
	  str		=>	sub{ '<> ?' },
	  bind		=>	sub{ return shift }		
	},
	'lt' => {
	  str		=>	sub{ '< ?' },
	  bind		=>	sub{ return shift },
	  num_cast	=>	0
	},
	'le' => {
	  str		=>	sub{ '<= ?' },
	  bind		=>	sub{ return shift },
	  num_cast	=>	0
	},
	'gt' => {
	  str		=>	sub{ '> ?' },
	  bind		=>	sub{ return shift },
	  num_cast	=>	0
	},
	'ge' => {
	  str		=>	sub{ '>= ?' },
	  bind		=>	sub{ return shift },
	  num_cast	=>	0
	},
 
	'bw' => {
	  str		=>	sub{ 'LIKE ?' },
	  bind		=>	sub { return(shift . "%") },
	  str_cast	=>	1,		
	},
	'bn' => {
	  str		=>	sub{ 'NOT LIKE ?' },
	  bind		=>	sub { return(shift . "%") },
	  str_cast	=>	1,
	},
	'in' => {
	  str		=>	sub{ 'IN(' . (  join ',', map{'?'}split(',', shift)) . ')'},
	  bind		=>	sub{ return split(',', shift) }
	},
	'ni' => {
	  str		=>	sub{ 'NOT IN(' . (  join ',', map{'?'}split(',', shift)) . ')' },
	  bind		=>	sub{ return  split(',', shift) }
	}, 
	'ew' => {
	  str		=>	sub{ 'LIKE ?' },
	  bind		=>	sub{ return("%" . shift) },
	  str_cast	=>	1,
	},
	'en' => {
	  str		=>	sub{ 'NOT LIKE ?' },
	  bind		=>	sub{ return("%" . shift) },
	  str_cast	=>	1,
	},
	'cn' => {
	  str 		=>	sub{ 'LIKE ?' },
	  bind		=>	sub{ return("%" . shift . "%") },
	  str_cast	=>	1,
	},
	'nc' => {
	  str 		=>	sub{ 'NOT LIKE ?' },
	  bind		=>	sub{ return("%" . shift . "%") },
	  str_cast	=>	1,
	},
	'nu' => {
	  str 		=>	sub{ 'IS NULL' }
	},
	'nn' => {
	  str 		=>	sub{ 'IS NOT NULL' }
	},
  );
 
  return \%filterops;
}
# translates search request from jqGrid to a 
# string of placeholders for mysql and array of corresponding bound values
# function input:
#- cgi object containing jqGrid request
#- hash that links jqGrid and MySQL names
#- type of wrapper that will be used 'AND', 'WHERE', 'HAVING' or empty string
# function output:
# hashref containing string and arrayref of bindvalues
 
sub get_searchstring{
  my ($cgi, $tbl_names, $str_init) = @_;
 
  my %tbl_names 	= %$tbl_names;
  my $searchstring 	= '';
  my @binds 		= ();
 
  my %filterops = %{_get_filterops()};
 
  my $searchrequest	= ($cgi->param('_search') eq 'true')?(get_data_from_json($cgi->param('filters'))):undef;
 
  if( $searchrequest ){
 
	my @searchoptions = ();
	my $groupOp = $$searchrequest{groupOp} eq 'AND'?'AND':'OR';
 
	foreach my $rule (@{$$searchrequest{rules}}){ # processing sequentially all pieces of jqgrid search request
	  next unless $filterops{ $$rule{op} } and $tbl_names{ $$rule{field} }; # if operation is incorrect or column is unknown
          # preparing string of placeholders      
	  push @searchoptions, ( $filterops{ $$rule{op} }{str_cast}? 	# should operand be casted to string?
					"CAST($tbl_names{ $$rule{field} } AS CHAR) ":			
					  $filterops{ $$rule{op} }{num_cast}?	# should operand be casted to number?
						"CAST($tbl_names{ $$rule{field} } AS UNSIGNED)":
						  "$tbl_names{ $$rule{field} } "  # by default operand is  used as is
				) . $filterops{ $$rule{op} }{str}->($$rule{data});  # to the right of the operand we are joining sql condition
 
	  push @binds, $filterops{ $$rule{op} }{bind}->($$rule{data}) if $filterops{ $$rule{op} }{bind}; # corresponding bind value(s)
	}
 
	$searchstring = join(" $groupOp ", @searchoptions); # joining all conditions as it was  selected in JqGrid search dialog
 
	if( $searchstring =~ /\?/ or $searchstring =~ /NULL/){ # if resulting string looks good
	  $searchstring = " $str_init (" . $searchstring . ") "; # adding wrapper
	}
	else{
	  $searchstring = "";
	}
  }
 
  return { srch_string => $searchstring, binds => \@binds};
 
}
 
sub send_json_data{	
  # hashref or arrayref
  my $ref = shift;
 
  my $json_xs = JSON::XS->new();
  $json_xs->ascii(1);
 
  my $outdata = $json_xs->encode( $ref );
 
  print "Status: 200 OK\n";
  print "Content-Length: " . length($outdata) . ";\n";
  print "Content-type: application/json\n\n";
 
  print $outdata;
 
  exit(0);
}
 
sub get_data_from_json{
  my $jsontext = shift;
 
  decode_entities($jsontext);
 
  my $json_xs = JSON::XS->new();
  $json_xs->ascii(1);
 
  return $json_xs->decode($jsontext);
}

And finally, here is the real working example.
You can run it on your own hosting after changing a few paths and deploying database.
You also should connect jqGrid and jQuery libs to your page, of course.
SQL dump
HTML file
CGI Perl file

1 Comment

  • nobodypi says:
    May 13, 2017 at 18:18

    Thank you, I have recently been searching for info about this subject for ages and yours is the best I have discovered till now. But, what about the bottom line? Are you sure about the source?
    nobodypi