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 $is_any = $key =~ s/:any//;
129 my @value_tokens = $is_multi || $is_any ? parse_line('\s+', 0, $value) : ($value);
131 ($key, $method) = split m{::}, $key, 2;
132 ($key, @filters) = split m{:}, $key;
136 for my $value_token (@value_tokens) {
139 $value_token = _apply($value_token, $_, $all_filters) for @filters;
140 $value_token = _apply($value_token, $method, $all_methods) if $method && exists $all_methods->{$method};
141 ($key, $value_token) = _apply_complex($key, $value_token, $method, $all_complex) if $method && exists $all_complex->{$method};
142 ($key, $value_token) = _dispatch_custom_filters($params{class}, $with_objects, $key, $value_token) if $params{class};
143 ($key, $value_token) = _apply_value_filters($key, $value_token, $type, $op);
145 push @args, $key, $value_token;
148 next unless defined $key;
150 push @result, $is_multi ? (and => [ @args ]) : $is_any ? (or => [ @args ]) : @args;
155 sub _apply_value_filters {
156 my ($key, $value, $type, $op) = @_;
158 return ($key, $value) unless $key && $value && $type && $op && (ref($value) eq 'HASH');
160 if (($type eq 'date') && ($op eq 'le')) {
161 my $date = delete $value->{le};
162 $value->{lt} = $date->add(days => 1);
165 return ($key, $value);
168 sub _dispatch_custom_filters {
169 my ($class, $with_objects, $key, $value) = @_;
171 # the key should by now have no filters left
172 # if it has, catch it here:
173 die 'unrecognized filters' if $key =~ /:/;
175 my @tokens = split /\./, $key;
176 my $curr_class = $class->object_class;
178 # our key will consist of dot-delimited tokens
179 # like this: order.part.unit.name
180 # each of these tokens except the last one is one of:
181 # - a relationship in the parent object
184 # the last token must be
186 # - a column in the parent object
188 # find first token which is not a relationship,
189 # so we can pass the rest on
191 while ($i < $#tokens) {
193 $curr_class = $curr_class->meta->relationship($tokens[$i])->class;
200 my $manager = $curr_class->meta->convention_manager->auto_manager_class_name;
201 my $obj_path = join '.', @tokens[0..$i-1];
202 my $obj_prefix = join '.', @tokens[0..$i-1], '';
203 my $key_token = $tokens[$i];
204 my @additional_tokens = @tokens[$i+1..$#tokens];
206 if ($manager->can('filter')) {
207 ($key, $value, my $obj) = $manager->filter($key_token, $value, $obj_prefix, $obj_path, @additional_tokens);
208 _add_uniq($with_objects, $obj) if $obj;
210 _add_uniq($with_objects, $obj_path) if $obj_path;
213 return ($key, $value);
217 my ($array, $what) = @_;
220 @$array = (uniq @$array, listify($what));
223 sub _collapse_indirect_filters {
224 my ($flattened) = @_;
226 die 'flattened filter array length is uneven, should be possible to use as hash' if @$flattened % 2;
228 my (%keys_to_delete, %keys_to_move, @collapsed);
230 # search keys matching /::$/;
231 for (my $i = 0; $i < scalar @$flattened; $i += 2) {
232 my ($key, $value) = ($flattened->[$i], $flattened->[$i+1]);
234 next unless $key =~ /^(.*\b)::$/;
236 $keys_to_delete{$key}++;
237 $keys_to_move{$1} = $1 . '::' . $value;
240 for (my $i = 0; $i < scalar @$flattened; $i += 2) {
241 my ($key, $value) = ($flattened->[$i], $flattened->[$i+1]);
243 if ($keys_to_move{$key}) {
244 push @collapsed, $keys_to_move{$key}, $value;
247 if (!$keys_to_delete{$key}) {
248 push @collapsed, $key, $value;
256 join '.', grep $_, @_;
260 my ($value, $name, $filters) = @_;
261 return $value unless $name && $filters->{$name};
262 return [ map { _apply($_, $name, $filters) } @$value ] if 'ARRAY' eq ref $value;
263 return $filters->{$name}->($value);
267 my ($key, $value, $name, $filters) = @_;
268 return $key, $value unless $name && $filters->{$name};
269 return $filters->{$name}->($key, $value);
282 SL::Controller::Helper::ParseFilter - Convert a form filter spec into a RDBO get_all filter
286 use SL::Controller::Helper::ParseFilter;
287 SL::DB::Manager::Object->get_all(parse_filter($::form->{filter}));
290 SL::DB::Manager::Object->get_all(parse_filter($::form->{filter},
291 with_objects => [ qw(part customer) ]));
295 A search filter will usually search for things in relations of the actual
296 search target. A search for sales orders may be filtered by the name of the
297 customer. L<Rose::DB::Object> allows you to search for these by filtering them prefixed with their table:
300 'customer.name' => 'John Doe',
301 'department.description' => { ilike => '%Sales%' },
302 'orddate' => { lt => DateTime->today },
305 Unfortunately, if you specify them in your form as these strings, the form
306 parser will convert them into nested structures like this:
316 And the substring match requires you to recognize the ilike, and modify the value.
318 C<parse_filter> tries to ease this by recognizing these structures and
319 providing suffixes for common search patterns.
325 =item C<parse_filter \%FILTER, [ %PARAMS ]>
327 First argument is the filter from form. It is highly recommended that you put
328 all filter attributes into a named container as to not confuse them with the
331 Nested structures will be parsed and interpreted as foreign references. For
332 example if you search for L<Order>s, this input will search for those with a
333 specific L<Salesman>:
335 [% L.select_tag('filter.salesman.id', ...) %]
337 Additionally you can add a modifier to the name to set a certain method:
339 [% L.input_tag('filter.department.description:substr::ilike', ...) %]
341 This will add the "% .. %" wildcards for substr matching in SQL, and add an
342 C<< ilike => $value >> block around it to match case insensitively.
344 As a rule all value filters require a single colon and must be placed before
345 match method suffixes, which are appended with 2 colons. See below for a full
352 Unfortunately Template cannot parse the postfixes if you want to
353 rerender the filter. For this reason all colon filter keys are by
354 default laundered into underscores, so you can use them like this:
356 [% L.input_tag('filter.price:number::lt', filter.price_number__lt) %]
358 Also Template has trouble when looking up the contents of arrays, so
359 these will get copied into a _ suffixed version as hashes:
361 [% L.checkbox_tag('filter.ids[]', value=15, checked=filter.ids_.15) %]
363 All of your original entries will stay intact. If you don't want this to
364 happen pass C<< no_launder => 1 >> as a parameter. Additionally you can pass a
365 different target for the laundered values with the C<launder_to> parameter. It
366 takes a hashref and will deep copy all values in your filter to the target. So
367 if you have a filter that looks like this:
370 'price:number::lt' => '2,30',
372 type => [ 'part', 'assembly' ],
377 parse_filter($filter, launder_to => $laundered_filter = { })
379 the original filter will be unchanged, and C<$laundered_filter> will end up
383 'price_number__lt' => '2,30',
385 'type_' => { part => 1, assembly => 1 },
388 =head1 INDIRECT FILTER METHODS
390 The reason for the method being last is that it is possible to specify the
391 method in another input. Suppose you want a date input and a separate
392 before/after/equal select, you can use the following:
394 [% L.date_tag('filter.appointed_date:date', ... ) %]
398 [% L.select_tag('filter.appointed_date:date::', ... ) %]
400 The special empty method will be used to set the method for the previous
403 =head1 CUSTOM FILTERS FROM OBJECTS
405 If the L<parse_filter> call contains a parameter C<class>, custom filters will
406 be honored. Suppose you have added a custom filter 'all' for parts which
407 expands to search both description and partnumber, the following
410 'part.all:substr::ilike' => 'A1',
417 part.description => { ilike => '%A1%' },
418 part.partnumber => { ilike => '%A1%' },
422 For more about custom filters, see L<SL::DB::Helper::Filtered>.
424 =head1 FILTERS (leading with :)
426 The following filters are built in, and can be used.
432 Parses the input string with C<< DateTime->from_lxoffice >>
436 Pasres the input string with C<< Form->parse_amount >>
440 Parses the input string with C<< Form->parse_amount / 100 >>
444 Removes whitespace characters (to be precice, characters with the \p{WSpace}
445 property from beginning and end of the value.
449 Adds "%" at the end of the string and applies C<trim>.
453 Adds "%" at the end of the string and applies C<trim>.
457 Adds "% .. %" around the search string and applies C<trim>.
461 =head2 METHODS (leading with ::)
473 All these are recognized like the L<Rose::DB::Object> methods.
477 If the value is undefined or an empty string then this parameter will
478 be completely removed from the query. Otherwise a falsish filter value
479 will match for C<NULL> and C<FALSE>; trueish values will only match
482 =item eq_ignore_empty
484 Ignores this item if it's empty. Otherwise compares it with the
485 standard SQL C<=> operator.
489 =head1 BUGS AND CAVEATS
491 This will not properly handle multiple versions of the same object in different
494 Suppose you want all L<SL::DB::Order>s which have either themselves a certain
495 customer, or are linked to a L<SL::DB::Invoice> with this customer, the
496 following will not work as you expect:
499 L.input_tag('customer.name:substr::ilike', ...)
500 L.input_tag('invoice.customer.name:substr::ilike', ...)
502 This will search for orders whose invoice has the _same_ customer, which matches
503 both inputs. This is because tables are aliased by their name and not by their
504 position in with_objects.
512 Additional filters should be pluggable.
518 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>