blib/lib/Catalyst/TraitFor/Controller/jQuery/jqGrid/Search.pm

filter: Statementstmt 95.9% 47 / 49 Branchbran 78.1% 25 / 32 Conditioncond 77.7% 7 / 9 MC/DCmcdc 66.6% 4 / 6 Subroutinesub 100% 5 / 5 Podpod 100% 1 / 1 totaltotal 87.2% 89 / 102 CC 21 SCAR 25.8
CC
21
Cov
88%
_complex_search
17.7
jqGrid_search
5.1
BEGIN
1.0
CRAP 13.1
↑ Summary
1 package Catalyst::TraitFor::Controller::jQuery::jqGrid::Search;
2  
3 4 ▶use 5.008;
Subroutine BEGIN: called CC=1 SCAR=0.0
4  
5 4 ▶use Moose::Role;
Subroutine BEGIN: called CC=1 SCAR=0.0
6 4 ▶use JSON;
Subroutine BEGIN: called CC=1 SCAR=0.0
7  
8 4 our $VERSION = '0.02';
9  
10 4 my %qOp = (
11 'eq' => { pre => '', post => '', op => '=', }, # equal
12 'ne' => { pre => '', post => '', op => '!=', }, # not equal
13 'lt' => { pre => '', post => '', op => '<', }, # less
14 'le' => { pre => '', post => '', op => '<=', }, # less or equal
15 'gt' => { pre => '', post => '', op => '>', }, # greater
16 'ge' => { pre => '', post => '', op => '>=', }, # greater or equal
17 'bw' => { pre => '', post => '%', op => '-like', }, # begins with
18 'bn' => { pre => '', post => '%', op => '-not_like', }, # does not begin with
19 'in' => { pre => '%', post => '%', op => '-like', }, # is in (reverse contains)
20 'ni' => { pre => '%', post => '%', op => '-not_like', }, # is not in (reverse does not contain)
21 'ew' => { pre => '%', post => '', op => '-like', }, # ends with
22 'en' => { pre => '%', post => '', op => '-not_like', }, # does not end with
23 'cn' => { pre => '%', post => '%', op => '-like', }, # contains
24 'nc' => { pre => '%', post => '%', op => '-not_like', }, # does not contain
25 );
26  
27 103 ▶sub _complex_search {
Subroutine _complex_search: called CC=17 SCAR=28.7
28 103 my ($cs_ref) = @_;
29 103 ▶ if ( ref $cs_ref eq 'HASH' ) {
Branch: 2 branches
BranchTrueFalseTotal
if (ref $cs_ref eq 'HASH') { } 51 52 103
elsif (ref $cs_ref eq 'ARRAY') { } 52 0 52
30  
31 # hash keys possible: groupOp, groups, rules
32 # in complex search, only groupOp is certain to be present
33 # (although a complex search with only a groupOp isn't really very complex...)
34  
35 51 ▶ if ( defined $cs_ref->{groupOp} ) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $cs_ref->{"groupOp"}) 51 0 51
36  
37 51 my $group_op = '-' . lc $cs_ref->{groupOp};
38  
39 51 my $group_aref;
40 51 ▶ $group_aref = _complex_search($cs_ref->{groups}) if defined $cs_ref->{groups} && @{$cs_ref->{groups}};
Branch: 1 branch
BranchTrueFalseTotal
if defined $cs_ref->{"groups"} and @{$cs_ref->{"groups"};} 28 23 51
Condition66%2/3
defined $cs_ref->{"groups"} and @{$cs_ref->{"groups"};}
!ll&&!rl&&r
02328
MC/DC50%1/2
defined $cs_ref->{"groups"} and @{$cs_ref->{"groups"};}
defined $cs_ref->{"groups"} @{$cs_ref->{"groups"};}
Truth table66%2/3
A and B
A= defined $cs_ref->{"groups"}B= @{$cs_ref->{"groups"};}
ABresult
0X0
100
111
41  
42 51 my $rule_aref;
43 51 ▶ $rule_aref = _complex_search($cs_ref->{rules}) if defined $cs_ref->{rules} && @{$cs_ref->{rules}};
Branch: 1 branch
BranchTrueFalseTotal
if defined $cs_ref->{"rules"} and @{$cs_ref->{"rules"};} 24 27 51
Condition66%2/3
defined $cs_ref->{"rules"} and @{$cs_ref->{"rules"};}
!ll&&!rl&&r
02724
MC/DC50%1/2
defined $cs_ref->{"rules"} and @{$cs_ref->{"rules"};}
defined $cs_ref->{"rules"} @{$cs_ref->{"rules"};}
Truth table66%2/3
A and B
A= defined $cs_ref->{"rules"}B= @{$cs_ref->{"rules"};}
ABresult
0X0
100
111
44  
45 51 ▶ if ( $group_aref && $rule_aref ) {
Branch: 2 branches
BranchTrueFalseTotal
if ($group_aref and $rule_aref) { } 8 43 51
elsif ($rule_aref) { } 16 27 43
Condition100%3/3
$group_aref and $rule_aref
!ll&&!rl&&r
23208
MC/DC100%2/2
$group_aref and $rule_aref
$group_aref $rule_aref
Truth table100%3/3
A and B
A= $group_arefB= $rule_aref
ABresult
0X0
100
111
46 8 push @{$group_aref}, $rule_aref;
47 }
48 elsif ( $rule_aref ) {
49 16 $group_aref = $rule_aref;
50 }
51 51 ▶ return { $group_op => $group_aref } if $group_aref;
Branch: 1 branch
BranchTrueFalseTotal
if $group_aref 44 7 51
52 }
53  
54 # empty search
55 7 return {};
56  
57 }
58 elsif ( ref $cs_ref eq 'ARRAY' ) {
59  
60 # array can be rules or groups, either is array of hashes
61 52 my $rg_aref;
62 52 for my $rg ( @{$cs_ref} ) {
63 71 ▶ if ( defined $rg->{groupOp} ) {
Branch: 2 branches
BranchTrueFalseTotal
if (defined $rg->{'groupOp'}) { } 31 40 71
elsif (defined $rg->{'field'}) { } 40 0 40
64  
65 # this one's a group
66 31 my $group_aref = _complex_search($rg);
67 31 ▶ push @{$rg_aref}, $group_aref if $group_aref;
Branch: 1 branch
BranchTrueFalseTotal
if $group_aref 31 0 31
68 }
69 elsif ( defined $rg->{field} ) {
70  
71 # this one's a rule, handle like simple search
72 my $rule_aref = jqGrid_search(
73 undef,
74 { _search => 'true',
75 searchField => $rg->{field},
76 searchOper => $rg->{op},
77 searchString => $rg->{data},
78 },
79 40 );
80 40 ▶ push @{$rg_aref}, $rule_aref if $rule_aref;
Branch: 1 branch
BranchTrueFalseTotal
if $rule_aref 40 0 40
81 }
82 else {
83 0 return 'not a jqGrid group/rule ARRAY'; # this shouldn't happen...
84 }
85 }
86 52 return $rg_aref;
87 }
88 } ## end sub _complex_search
89  
90 74 ▶sub jqGrid_search {
Subroutine jqGrid_search: called CC=5 SCAR=16.3
Pod documented
91 74 my ( $self, $params ) = @_;
92 74 ▶ return {} unless $params->{_search} eq 'true';
Branch: 1 branch
BranchTrueFalseTotal
unless $params->{"_search"} eq "true" 0 74 74
93 74 ▶ if ( $params->{filters} ) {
Branch: 2 branches
BranchTrueFalseTotal
if ($params->{'filters'}) { } 20 54 74
elsif ($params->{'searchField'}) { } 54 0 54
94 20 return _complex_search( JSON->new->decode( $params->{filters} ) );
95 }
96 elsif ( $params->{searchField} ) {
97  
98 # Simple search
99 54 ▶ if ( $params->{searchOper} =~ /i/ ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($params->{"searchOper"} =~ /i/) 2 52 54
100  
101 # 'in'/'ni' search - reverse field & value
102 2 my $temp = $params->{searchField};
103 2 $params->{searchField} = $params->{searchString};
104 2 $params->{searchString} = $temp;
105 }
106 return { $params->{searchField} =>
107 { $qOp{ $params->{searchOper} }{op} => $qOp{ $params->{searchOper} }{pre} . $params->{searchString} . $qOp{ $params->{searchOper} }{post} }
108 54 };
109 }
110 else {
111 # unknown search type
112 0 return {};
113 }
114 }
115  
116 =head1 NAME
117  
118 Catalyst::TraitFor::Controller::jQuery::jqGrid::Search - Catalyst helper function for translating jqGrid search parameters
119  
120 =head1 VERSION
121  
122 Version 0.02
123  
124 =head1 SYNOPSIS
125  
126 Helper for translating search queries from the jQuery plugin jqGrid.
127  
128 In your Catalyst Controller.
129  
130 package MyApp::Web::Controller::Root;
131  
132 use Moose;
133 use namespace::autoclean;
134  
135 with 'Catalyst::TraitFor::Controller::jQuery::jqGrid::Search';
136  
137 Then later on in your controllers you can do
138  
139 sub foo :Local {
140 my ($self, $c) = @_;
141  
142 my $search_filter = $self->jqGrid_search($c->req->params);
143  
144 my $bar_rs = $c->model('DB::Baz')->search(
145 $search_filter,
146 {},
147 );
148  
149 =head1 DESCRIPTION
150  
151 The L<http://jquery.com/> Javascript library simplifies the writing of
152 Javascript and does for Javascript what the MVC model does for Perl.
153  
154 A very useful plugin to jQuery is a Grid control which can be used to page
155 through data obtained from a back-end database. Ajax calls to the back-end
156 retrieve JSON data. See L<http://www.trirand.com/blog/>
157  
158 This module provides a helper function to translate the jqGrid simple and/or
159 complex search query strings to the L<DBIx::Class|DBIx::Class/> /
160 L<SQL::Abstract|SQL::Abstract/> search/where constructs.
161  
162 =head1 SUBROUTINES/METHODS
163  
164 =head2 jqGrid_search
165  
166 =head3 Simple Search (single field)
167  
168 jqGrid submits the parameters C<searchField>, C<searchOper>, and C<searchString>.
169 For example, the query "B<cust_name = 'Bob'>" would set:
170  
171 searchField = 'cust_name'
172 searchOper = 'eq'
173 searchString = 'Bob'
174  
175 jqGrid_search translates that into:
176  
177 { 'cust_name' => { '=' => 'Bob' } }
178  
179 =head3 Complex Search
180  
181 jqGrid submits the parameter C<filters> with JSON-encoded data.
182 For example, the query: "B<( (name LIKE "%Bob%" AND tax E<gt>= 20) OR
183 (note LIKE "no tax%" AND amount E<lt> 1000) )>"
184 would result in the following:
185  
186 filters = '{"groupOp":"OR","rules":[],"groups":[{"groupOp":"AND","rules":[{"field":"name","op":"cn","data":"Bob"},
187 {"field":"tax","op":"ge","data":20}],"groups":[]},{"groupOp":"AND","rules":[{"field":"note","op":"bw",
188 "data":"no tax"},{"field":"amount","op":"lt","data":"1000"}],"groups":[]}]}'
189  
190 jqGrid_search translates that into:
191  
192 {
193 '-or' => [
194 {
195 '-and' => [
196 { 'name' => { '-like' => '%Bob%' } },
197 { 'tax' => { '>=' => '20' } }
198 ]
199 },
200 {
201 '-and' => [
202 { 'note' => { '-like' => 'no tax%' } },
203 { 'amount' => { '<' => '1000' } }
204 ]
205 }
206 ]
207 }
208  
209 =head3 The jqGrid Search Operators are:
210  
211 =over
212  
213 =item * eq ( equal )
214  
215 ... WHERE searchField = 'searchString'
216  
217 =item * ne ( not equal )
218  
219 ... WHERE searchField != 'searchString'
220  
221 =item * lt ( less )
222  
223 ... WHERE searchField < searchString
224  
225 =item * le ( less or equal )
226  
227 ... WHERE searchField <= searchString
228  
229 =item * gt ( greater )
230  
231 ... WHERE searchField > searchString
232  
233 =item * ge ( greater or equal )
234  
235 ... WHERE searchField >= searchString
236  
237 =item * bw ( begins with )
238  
239 ... WHERE searchField like 'searchString%'
240  
241 =item * bn ( does not begin with )
242  
243 ... WHERE searchField not like 'searchString%'
244  
245 =item * in ( is in )
246  
247 According to L<http://stackoverflow.com/questions/9383267/what-is-the-usage-of-jqgrid-search-is-in-and-is-not-in> 'in' and 'ni' are
248 not set-based operators (C<WHERE field IN (val1,val2,val3)>) but are: "I<... the equivalents of contains and does not contain, with the operands reversed>", thus:
249  
250 ... WHERE searchB<String> like '%searchB<Field>%'
251  
252 =item * ni ( is not in )
253  
254 ... WHERE searchB<String> not like '%searchB<Field>%'
255  
256 =item * ew ( ends with )
257  
258 ... WHERE searchField like '%searchString'
259  
260 =item * en ( does not end with )
261  
262 ... WHERE searchField not like '%searchString'
263  
264 =item * cn ( contains )
265  
266 ... WHERE searchField like '%searchString%'
267  
268 =item * nc ( does not contain )
269  
270 ... WHERE searchField not like '%searchString%'
271  
272 =back
273  
274 =head3 jqGrid Search Setup
275  
276 In your jqGrid colModel options, be sure to set sensible search operators for each field in the B<sopt> option within B<searchoptions>.
277 For example, the various B<like>-like operators (B<bw>, B<ew>, etc.) probably don't make sense for a numeric field. Similarly, anything other
278 than B<eq> for a boolean field is unnecessary.
279  
280 =head1 DEPENDENCIES
281  
282 JSON - for parsing the jqGrid "complex search" parameter C<filters>.
283  
284 =head1 AUTHOR
285  
286 Scott R. Keszler, C<< <keszler at srkconsulting.com> >>
287  
288 =head1 BUGS AND LIMITATIONS
289  
290 Please report any bugs or feature requests to C<bug-catalyst-traitfor-controller-jquery-jqgrid-search at rt.cpan.org>, or through
291 the web interface at L<http://rt.cpan.org/NoAuth/ReportBug.html?Queue=Catalyst-TraitFor-Controller-jQuery-jqGrid-Search>. I will be notified, and then you'll
292 automatically be notified of progress on your bug as I make changes.
293  
294 =head1 SUPPORT
295  
296 You can find documentation for this module with the perldoc command.
297  
298 perldoc Catalyst::TraitFor::Controller::jQuery::jqGrid::Search
299  
300 You can also look for information at:
301  
302 =over 4
303  
304 =item * RT: CPAN's request tracker (report bugs here)
305  
306 L<http://rt.cpan.org/NoAuth/Bugs.html?Dist=Catalyst-TraitFor-Controller-jQuery-jqGrid-Search>
307  
308 =item * AnnoCPAN: Annotated CPAN documentation
309  
310 L<http://annocpan.org/dist/Catalyst-TraitFor-Controller-jQuery-jqGrid-Search>
311  
312 =item * CPAN Ratings
313  
314 L<http://cpanratings.perl.org/d/Catalyst-TraitFor-Controller-jQuery-jqGrid-Search>
315  
316 =item * Search CPAN
317  
318 L<http://search.cpan.org/dist/Catalyst-TraitFor-Controller-jQuery-jqGrid-Search/>
319  
320 =back
321  
322 =head1 ACKNOWLEDGEMENTS
323  
324 Thanks to Ian Docherty <pause@iandocherty.com> for L<Catalyst::TraitFor::Controller::jQuery::jqGrid|Catalyst::TraitFor::Controller::jQuery::jqGrid>,
325 which I used as a template for this code.
326  
327 =head1 LICENSE AND COPYRIGHT
328  
329 Copyright 2012 Scott R. Keszler.
330  
331 This program is free software; you can redistribute it and/or modify it
332 under the terms of either: the GNU General Public License as published
333 by the Free Software Foundation; or the Artistic License.
334  
335 See L<http://dev.perl.org/licenses/> for more information.
336  
337 =cut
338  
339 4 1; # End of Catalyst::TraitFor::Controller::jQuery::jqGrid::Search
↑ Summary