Catalyst::TraitFor::Controller::jQuery::jqGrid::Search - Catalyst helper function for translating jqGrid search parameters
Version 0.02
Helper for translating search queries from the jQuery plugin jqGrid.
In your Catalyst Controller.
package MyApp::Web::Controller::Root; use Moose; use namespace::autoclean; with 'Catalyst::TraitFor::Controller::jQuery::jqGrid::Search';
Then later on in your controllers you can do
sub foo :Local { my ($self, $c) = @_; my $search_filter = $self->jqGrid_search($c->req->params); my $bar_rs = $c->model('DB::Baz')->search( $search_filter, {}, );
The http://jquery.com/ Javascript library simplifies the writing of Javascript and does for Javascript what the MVC model does for Perl.
A very useful plugin to jQuery is a Grid control which can be used to page through data obtained from a back-end database. Ajax calls to the back-end retrieve JSON data. See http://www.trirand.com/blog/
This module provides a helper function to translate the jqGrid simple and/or complex search query strings to the DBIx::Class / SQL::Abstract search/where constructs.
jqGrid submits the parameters searchField, searchOper, and searchString. For example, the query "cust_name = 'Bob'" would set:
searchField
searchOper
searchString
searchField = 'cust_name' searchOper = 'eq' searchString = 'Bob'
jqGrid_search translates that into:
{ 'cust_name' => { '=' => 'Bob' } }
jqGrid submits the parameter filters with JSON-encoded data. For example, the query: "( (name LIKE "%Bob%" AND tax >= 20) OR (note LIKE "no tax%" AND amount < 1000) )" would result in the following:
filters
filters = '{"groupOp":"OR","rules":[],"groups":[{"groupOp":"AND","rules":[{"field":"name","op":"cn","data":"Bob"}, {"field":"tax","op":"ge","data":20}],"groups":[]},{"groupOp":"AND","rules":[{"field":"note","op":"bw", "data":"no tax"},{"field":"amount","op":"lt","data":"1000"}],"groups":[]}]}'
{ '-or' => [ { '-and' => [ { 'name' => { '-like' => '%Bob%' } }, { 'tax' => { '>=' => '20' } } ] }, { '-and' => [ { 'note' => { '-like' => 'no tax%' } }, { 'amount' => { '<' => '1000' } } ] } ] }
eq ( equal )
... WHERE searchField = 'searchString'
ne ( not equal )
... WHERE searchField != 'searchString'
lt ( less )
... WHERE searchField < searchString
le ( less or equal )
... WHERE searchField <= searchString
gt ( greater )
... WHERE searchField > searchString
ge ( greater or equal )
... WHERE searchField >= searchString
bw ( begins with )
... WHERE searchField like 'searchString%'
bn ( does not begin with )
... WHERE searchField not like 'searchString%'
in ( is in )
According to http://stackoverflow.com/questions/9383267/what-is-the-usage-of-jqgrid-search-is-in-and-is-not-in 'in' and 'ni' are not set-based operators (WHERE field IN (val1,val2,val3)) but are: "... the equivalents of contains and does not contain, with the operands reversed", thus:
WHERE field IN (val1,val2,val3)
... WHERE searchString like '%searchField%'
ni ( is not in )
... WHERE searchString not like '%searchField%'
ew ( ends with )
... WHERE searchField like '%searchString'
en ( does not end with )
... WHERE searchField not like '%searchString'
cn ( contains )
... WHERE searchField like '%searchString%'
nc ( does not contain )
... WHERE searchField not like '%searchString%'
In your jqGrid colModel options, be sure to set sensible search operators for each field in the sopt option within searchoptions. For example, the various like-like operators (bw, ew, etc.) probably don't make sense for a numeric field. Similarly, anything other than eq for a boolean field is unnecessary.
JSON - for parsing the jqGrid "complex search" parameter filters.
Scott R. Keszler, <keszler at srkconsulting.com>
<keszler at srkconsulting.com>
Please report any bugs or feature requests to bug-catalyst-traitfor-controller-jquery-jqgrid-search at rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=Catalyst-TraitFor-Controller-jQuery-jqGrid-Search. I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.
bug-catalyst-traitfor-controller-jquery-jqgrid-search at rt.cpan.org
You can find documentation for this module with the perldoc command.
perldoc Catalyst::TraitFor::Controller::jQuery::jqGrid::Search
You can also look for information at:
RT: CPAN's request tracker (report bugs here)
http://rt.cpan.org/NoAuth/Bugs.html?Dist=Catalyst-TraitFor-Controller-jQuery-jqGrid-Search
AnnoCPAN: Annotated CPAN documentation
http://annocpan.org/dist/Catalyst-TraitFor-Controller-jQuery-jqGrid-Search
CPAN Ratings
http://cpanratings.perl.org/d/Catalyst-TraitFor-Controller-jQuery-jqGrid-Search
Search CPAN
http://search.cpan.org/dist/Catalyst-TraitFor-Controller-jQuery-jqGrid-Search/
Thanks to Ian Docherty <pause@iandocherty.com> for Catalyst::TraitFor::Controller::jQuery::jqGrid, which I used as a template for this code.
Copyright 2012 Scott R. Keszler.
This program is free software; you can redistribute it and/or modify it under the terms of either: the GNU General Public License as published by the Free Software Foundation; or the Artistic License.
See http://dev.perl.org/licenses/ for more information.
To install Catalyst::TraitFor::Controller::jQuery::jqGrid::Search, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Catalyst::TraitFor::Controller::jQuery::jqGrid::Search
CPAN shell
perl -MCPAN -e shell install Catalyst::TraitFor::Controller::jQuery::jqGrid::Search
For more information on module installation, please visit the detailed CPAN module installation guide.