/ 
Catalyst-TraitFor-Controller-jQuery-jqGrid-Search-0.02
River stage zero No dependents
/ Catalyst::TraitFor::Controller::jQuery::jqGrid::Search

NAME

Catalyst::TraitFor::Controller::jQuery::jqGrid::Search - Catalyst helper function for translating jqGrid search parameters

VERSION

Version 0.02

SYNOPSIS

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,
    {},
  );

DESCRIPTION

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.

SUBROUTINES/METHODS

Simple Search (single field)

jqGrid submits the parameters searchField, searchOper, and searchString. For example, the query "cust_name = 'Bob'" would set:

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 = '{"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":[]}]}'

jqGrid_search translates that into:

{
  '-or' => [
    {
      '-and' => [
        { 'name'   => { '-like' => '%Bob%' } },
        { 'tax'    => { '>=' => '20' } }
      ]
    },
    {
      '-and' => [
        { 'note'   => { '-like' => 'no tax%' } },
        { 'amount' => { '<' => '1000' } }
      ]
    }
  ]
}

The jqGrid Search Operators are:

  • 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 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%'

jqGrid Search Setup

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.

DEPENDENCIES

JSON - for parsing the jqGrid "complex search" parameter filters.

AUTHOR

Scott R. Keszler, <keszler at srkconsulting.com>

BUGS AND LIMITATIONS

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.

SUPPORT

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:

ACKNOWLEDGEMENTS

Thanks to Ian Docherty <pause@iandocherty.com> for Catalyst::TraitFor::Controller::jQuery::jqGrid, which I used as a template for this code.

LICENSE AND COPYRIGHT

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.