1 package SL::Controller::Helper::ParseFilter;
5 use Exporter qw(import);
6 our @EXPORT = qw(parse_filter);
9 use SL::Helper::DateTime;
10 use List::MoreUtils qw(uniq);
11 use SL::Util qw(trim);
12 use SL::MoreCommon qw(listify);
17 my ($key, $value) = @_;
19 return () if ($value // '') eq '';
20 return (or => [ $key => undef, $key => 0 ]) if !$value;
25 date => sub { DateTime->from_lxoffice($_[0]) },
26 number => sub { $::form->parse_amount(\%::myconfig, $_[0]) },
27 percent => sub { $::form->parse_amount(\%::myconfig, $_[0]) / 100 },
28 head => sub { trim($_[0]) . '%' },
29 tail => sub { '%' . trim($_[0]) },
30 substr => sub { '%' . trim($_[0]) . '%' },
31 trim => sub { trim($_[0]) },
35 enable => sub { ;;;; },
36 eq_ignore_empty => sub { ($_[0] // '') eq '' ? () : +{ eq => $_[0] } },
38 # since $_ is an alias it can't be used in a closure. even "".$_ or "$_"
39 # does not work, we need a real copy.
41 $_ => sub { +{ $_copy => $_[0] } },
42 } qw(similar match imatch regex regexp like ilike rlike is is_not ne eq lt gt le ge),
45 my %complex_methods = (
46 lazy_bool_eq => \&_lazy_bool_eq,
50 my ($filter, %params) = @_;
52 my $objects = $params{with_objects} || [];
54 my ($flattened, $auto_objects) = flatten($filter, '', %params);
56 if (!$params{class}) {
57 _add_uniq($objects, $_) for @$auto_objects;
60 _launder_keys($filter, $params{launder_to}) unless $params{no_launder};
62 my $query = _parse_filter($flattened, $objects, %params);
65 ($query && @$query ? (query => $query) : ()),
66 ($objects && @$objects ? ( with_objects => [ uniq @$objects ]) : ());
70 my ($filter, $launder_to) = @_;
71 $launder_to ||= $filter;
72 return unless ref $filter eq 'HASH';
73 for my $key (keys %$filter) {
76 if ('' eq ref $filter->{$orig}) {
77 $launder_to->{$key} = $filter->{$orig};
78 } elsif ('ARRAY' eq ref $filter->{$orig}) {
79 $launder_to->{"${key}_"} = { map { $_ => 1 } @{ $filter->{$orig} } };
81 $launder_to->{$key} ||= { };
82 _launder_keys($filter->{$key}, $launder_to->{$key});
88 my ($filter, $prefix, %params) = @_;
90 return (undef, []) unless 'HASH' eq ref $filter;
91 my $with_objects = [];
95 while (my ($key, $value) = each %$filter) {
96 next if !defined $value || $value eq ''; # 0 is fine
97 if ('HASH' eq ref $value) {
98 my ($query, $more_objects) = flatten($value, _prefix($prefix, $key));
99 push @result, @$query if $query;
100 _add_uniq($with_objects, $_) for _prefix($prefix, $key), @$more_objects;
102 push @result, _prefix($prefix, $key) => $value;
106 return \@result, $with_objects;
110 my ($flattened, $with_objects, %params) = @_;
112 return () unless 'ARRAY' eq ref $flattened;
114 $flattened = _collapse_indirect_filters($flattened);
116 my $all_filters = { %filters, %{ $params{filters} || {} } };
117 my $all_methods = { %methods, %{ $params{methods} || {} } };
118 my $all_complex = { %complex_methods, %{ $params{complex_methods} || {} } };
121 for (my $i = 0; $i < scalar @$flattened; $i += 2) {
122 my (@args, @filters, $method);
124 my ($key, $value) = ($flattened->[$i], $flattened->[$i+1]);
125 my ($type, $op) = $key =~ m{:(.+)::(.+)};
127 my $is_multi = $key =~ s/:multi//;
128 my @value_tokens = $is_multi ? parse_line('\s+', 0, $value) : ($value);
130 ($key, $method) = split m{::}, $key, 2;
131 ($key, @filters) = split m{:}, $key;
135 for my $value_token (@value_tokens) {
138 $value_token = _apply($value_token, $_, $all_filters) for @filters;
139 $value_token = _apply($value_token, $method, $all_methods) if $method && exists $all_methods->{$method};
140 ($key, $value_token) = _apply_complex($key, $value_token, $method, $all_complex) if $method && exists $all_complex->{$method};
141 ($key, $value_token) = _dispatch_custom_filters($params{class}, $with_objects, $key, $value_token) if $params{class};
142 ($key, $value_token) = _apply_value_filters($key, $value_token, $type, $op);
144 push @args, $key, $value_token;
147 next unless defined $key;
149 push @result, $is_multi ? (and => [ @args ]) : @args;
154 sub _apply_value_filters {
155 my ($key, $value, $type, $op) = @_;
157 return ($key, $value) unless $key && $value && $type && $op && (ref($value) eq 'HASH');
159 if (($type eq 'date') && ($op eq 'le')) {
160 my $date = delete $value->{le};
161 $value->{lt} = $date->add(days => 1);
164 return ($key, $value);
167 sub _dispatch_custom_filters {
168 my ($class, $with_objects, $key, $value) = @_;
170 # the key should by now have no filters left
171 # if it has, catch it here:
172 die 'unrecognized filters' if $key =~ /:/;
174 my @tokens = split /\./, $key;
175 my $curr_class = $class->object_class;
177 # our key will consist of dot-delimited tokens
178 # like this: order.part.unit.name
179 # each of these tokens except the last one is one of:
180 # - a relationship in the parent object
183 # the last token must be
185 # - a column in the parent object
187 # find first token which is not a relationship,
188 # so we can pass the rest on
190 while ($i < $#tokens) {
192 $curr_class = $curr_class->meta->relationship($tokens[$i])->class;
199 my $manager = $curr_class->meta->convention_manager->auto_manager_class_name;
200 my $obj_path = join '.', @tokens[0..$i-1];
201 my $obj_prefix = join '.', @tokens[0..$i-1], '';
202 my $key_token = $tokens[$i];
203 my @additional_tokens = @tokens[$i+1..$#tokens];
205 if ($manager->can('filter')) {
206 ($key, $value, my $obj) = $manager->filter($key_token, $value, $obj_prefix, $obj_path, @additional_tokens);
207 _add_uniq($with_objects, $obj) if $obj;
209 _add_uniq($with_objects, $obj_path) if $obj_path;
212 return ($key, $value);
216 my ($array, $what) = @_;
219 @$array = (uniq @$array, listify($what));
222 sub _collapse_indirect_filters {
223 my ($flattened) = @_;
225 die 'flattened filter array length is uneven, should be possible to use as hash' if @$flattened % 2;
227 my (%keys_to_delete, %keys_to_move, @collapsed);
229 # search keys matching /::$/;
230 for (my $i = 0; $i < scalar @$flattened; $i += 2) {
231 my ($key, $value) = ($flattened->[$i], $flattened->[$i+1]);
233 next unless $key =~ /^(.*\b)::$/;
235 $keys_to_delete{$key}++;
236 $keys_to_move{$1} = $1 . '::' . $value;
239 for (my $i = 0; $i < scalar @$flattened; $i += 2) {
240 my ($key, $value) = ($flattened->[$i], $flattened->[$i+1]);
242 if ($keys_to_move{$key}) {
243 push @collapsed, $keys_to_move{$key}, $value;
246 if (!$keys_to_delete{$key}) {
247 push @collapsed, $key, $value;
255 join '.', grep $_, @_;
259 my ($value, $name, $filters) = @_;
260 return $value unless $name && $filters->{$name};
261 return [ map { _apply($_, $name, $filters) } @$value ] if 'ARRAY' eq ref $value;
262 return $filters->{$name}->($value);
266 my ($key, $value, $name, $filters) = @_;
267 return $key, $value unless $name && $filters->{$name};
268 return $filters->{$name}->($key, $value);
277 SL::Controller::Helper::ParseFilter - Convert a form filter spec into a RDBO get_all filter
281 use SL::Controller::Helper::ParseFilter;
282 SL::DB::Manager::Object->get_all(parse_filter($::form->{filter}));
285 SL::DB::Manager::Object->get_all(parse_filter($::form->{filter},
286 with_objects => [ qw(part customer) ]));
290 A search filter will usually search for things in relations of the actual
291 search target. A search for sales orders may be filtered by the name of the
292 customer. L<Rose::DB::Object> allows you to search for these by filtering them prefixed with their table:
295 'customer.name' => 'John Doe',
296 'department.description' => [ ilike => '%Sales%' ],
297 'orddate' => [ lt => DateTime->today ],
300 Unfortunately, if you specify them in your form as these strings, the form
301 parser will convert them into nested structures like this:
311 And the substring match requires you to recognize the ilike, and modify the value.
313 C<parse_filter> tries to ease this by recognizing these structures and
314 providing suffixes for common search patterns.
320 =item C<parse_filter \%FILTER, [ %PARAMS ]>
322 First argument is the filter from form. It is highly recommended that you put
323 all filter attributes into a named container as to not confuse them with the
326 Nested structures will be parsed and interpreted as foreign references. For
327 example if you search for L<Order>s, this input will search for those with a
328 specific L<Salesman>:
330 [% L.select_tag('filter.salesman.id', ...) %]
332 Additionally you can add a modifier to the name to set a certain method:
334 [% L.input_tag('filter.department.description:substr::ilike', ...) %]
336 This will add the "% .. %" wildcards for substr matching in SQL, and add an
337 C<< ilike => $value >> block around it to match case insensitively.
339 As a rule all value filters require a single colon and must be placed before
340 match method suffixes, which are appended with 2 colons. See below for a full
347 Unfortunately Template cannot parse the postfixes if you want to
348 rerender the filter. For this reason all colon filter keys are by
349 default laundered into underscores, so you can use them like this:
351 [% L.input_tag('filter.price:number::lt', filter.price_number__lt) %]
353 Also Template has trouble when looking up the contents of arrays, so
354 these will get copied into a _ suffixed version as hashes:
356 [% L.checkbox_tag('filter.ids[]', value=15, checked=filter.ids_.15) %]
358 All of your original entries will stay intact. If you don't want this to
359 happen pass C<< no_launder => 1 >> as a parameter. Additionally you can pass a
360 different target for the laundered values with the C<launder_to> parameter. It
361 takes a hashref and will deep copy all values in your filter to the target. So
362 if you have a filter that looks like this:
365 'price:number::lt' => '2,30',
367 type => [ 'part', 'assembly' ],
372 parse_filter($filter, launder_to => $laundered_filter = { })
374 the original filter will be unchanged, and C<$laundered_filter> will end up
378 'price_number__lt' => '2,30',
380 'type_' => { part => 1, assembly => 1 },
383 =head1 INDIRECT FILTER METHODS
385 The reason for the method being last is that it is possible to specify the
386 method in another input. Suppose you want a date input and a separate
387 before/after/equal select, you can use the following:
389 [% L.date_tag('filter.appointed_date:date', ... ) %]
393 [% L.select_tag('filter.appointed_date:date::', ... ) %]
395 The special empty method will be used to set the method for the previous
398 =head1 CUSTOM FILTERS FROM OBJECTS
400 If the L<parse_filter> call contains a parameter C<class>, custom filters will
401 be honored. Suppose you have added a custom filter 'all' for parts which
402 expands to search both description and partnumber, the following
405 'part.all:substr::ilike' => 'A1',
412 part.description => { ilike => '%A1%' },
413 part.partnumber => { ilike => '%A1%' },
417 For more about custom filters, see L<SL::DB::Helper::Filtered>.
419 =head1 FILTERS (leading with :)
421 The following filters are built in, and can be used.
427 Parses the input string with C<< DateTime->from_lxoffice >>
431 Pasres the input string with C<< Form->parse_amount >>
435 Parses the input string with C<< Form->parse_amount / 100 >>
439 Removes whitespace characters (to be precice, characters with the \p{WSpace}
440 property from beginning and end of the value.
444 Adds "%" at the end of the string and applies C<trim>.
448 Adds "%" at the end of the string and applies C<trim>.
452 Adds "% .. %" around the search string and applies C<trim>.
456 =head2 METHODS (leading with ::)
468 All these are recognized like the L<Rose::DB::Object> methods.
472 If the value is undefined or an empty string then this parameter will
473 be completely removed from the query. Otherwise a falsish filter value
474 will match for C<NULL> and C<FALSE>; trueish values will only match
477 =item eq_ignore_empty
479 Ignores this item if it's empty. Otherwise compares it with the
480 standard SQL C<=> operator.
484 =head1 BUGS AND CAVEATS
486 This will not properly handle multiple versions of the same object in different
489 Suppose you want all L<SL::DB::Order>s which have either themselves a certain
490 customer, or are linked to a L<SL::DB::Invoice> with this customer, the
491 following will not work as you expect:
494 L.input_tag('customer.name:substr::ilike', ...)
495 L.input_tag('invoice.customer.name:substr::ilike', ...)
497 This will search for orders whose invoice has the _same_ customer, which matches
498 both inputs. This is because tables are aliased by their name and not by their
499 position in with_objects.
507 Additional filters should be pluggable.
513 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>