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);
281 SL::Controller::Helper::ParseFilter - Convert a form filter spec into a RDBO get_all filter
285 use SL::Controller::Helper::ParseFilter;
286 SL::DB::Manager::Object->get_all(parse_filter($::form->{filter}));
289 SL::DB::Manager::Object->get_all(parse_filter($::form->{filter},
290 with_objects => [ qw(part customer) ]));
294 A search filter will usually search for things in relations of the actual
295 search target. A search for sales orders may be filtered by the name of the
296 customer. L<Rose::DB::Object> allows you to search for these by filtering them prefixed with their table:
299 'customer.name' => 'John Doe',
300 'department.description' => [ like => '%Sales%' ],
301 '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>