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::MoreCommon qw(listify);
16 date => sub { DateTime->from_lxoffice($_[0]) },
17 number => sub { $::form->parse_amount(\%::myconfig, $_[0]) },
18 percent => sub { $::form->parse_amount(\%::myconfig, $_[0]) / 100 },
19 head => sub { $_[0] . '%' },
20 tail => sub { '%' . $_[0] },
21 substr => sub { '%' . $_[0] . '%' },
25 enable => sub { ;;;; },
26 eq_ignore_empty => sub { ($_[0] // '') eq '' ? () : +{ eq => $_[0] } },
28 # since $_ is an alias it can't be used in a closure. even "".$_ or "$_"
29 # does not work, we need a real copy.
31 $_ => sub { +{ $_copy => $_[0] } },
32 } qw(similar match imatch regex regexp like ilike rlike is is_not ne eq lt gt le ge),
36 my ($filter, %params) = @_;
38 my $objects = $params{with_objects} || [];
40 my ($flattened, $auto_objects) = flatten($filter, '', %params);
42 if (!$params{class}) {
43 _add_uniq($objects, $_) for @$auto_objects;
46 _launder_keys($filter, $params{launder_to}) unless $params{no_launder};
48 my $query = _parse_filter($flattened, $objects, %params);
51 ($query && @$query ? (query => $query) : ()),
52 ($objects && @$objects ? ( with_objects => [ uniq @$objects ]) : ());
56 my ($filter, $launder_to) = @_;
57 $launder_to ||= $filter;
58 return unless ref $filter eq 'HASH';
59 for my $key (keys %$filter) {
62 if ('' eq ref $filter->{$orig}) {
63 $launder_to->{$key} = $filter->{$orig};
64 } elsif ('ARRAY' eq ref $filter->{$orig}) {
65 $launder_to->{"${key}_"} = { map { $_ => 1 } @{ $filter->{$orig} } };
67 $launder_to->{$key} ||= { };
68 _launder_keys($filter->{$key}, $launder_to->{$key});
74 my ($filter, $prefix, %params) = @_;
76 return (undef, []) unless 'HASH' eq ref $filter;
77 my $with_objects = [];
81 while (my ($key, $value) = each %$filter) {
82 next if !defined $value || $value eq ''; # 0 is fine
83 if ('HASH' eq ref $value) {
84 my ($query, $more_objects) = flatten($value, _prefix($prefix, $key));
85 push @result, @$query if $query;
86 _add_uniq($with_objects, $_) for _prefix($prefix, $key), @$more_objects;
88 push @result, _prefix($prefix, $key) => $value;
92 return \@result, $with_objects;
96 my ($flattened, $with_objects, %params) = @_;
98 return () unless 'ARRAY' eq ref $flattened;
100 $flattened = _collapse_indirect_filters($flattened);
102 my $all_filters = { %filters, %{ $params{filters} || {} } };
103 my $all_methods = { %methods, %{ $params{methods} || {} } };
106 for (my $i = 0; $i < scalar @$flattened; $i += 2) {
107 my (@args, @filters, @methods);
109 my ($key, $value) = ($flattened->[$i], $flattened->[$i+1]);
110 my ($type, $op) = $key =~ m{:(.+)::(.+)};
112 my $is_multi = $key =~ s/:multi//;
113 my @value_tokens = $is_multi ? parse_line('\s+', 0, $value) : ($value);
115 ($key, @methods) = split m{::}, $key;
116 ($key, @filters) = split m{:}, $key;
120 for my $value_token (@value_tokens) {
123 $value_token = _apply($value_token, $_, $all_filters) for @filters;
124 $value_token = _apply($value_token, $_, $all_methods) for @methods;
125 ($key, $value_token) = _dispatch_custom_filters($params{class}, $with_objects, $key, $value_token) if $params{class};
126 ($key, $value_token) = _apply_value_filters($key, $value_token, $type, $op);
128 push @args, $key, $value_token;
131 next unless defined $key;
133 push @result, $is_multi ? (and => [ @args ]) : @args;
138 sub _apply_value_filters {
139 my ($key, $value, $type, $op) = @_;
141 return ($key, $value) unless $key && $value && $type && $op && (ref($value) eq 'HASH');
143 if (($type eq 'date') && ($op eq 'le')) {
144 my $date = delete $value->{le};
145 $value->{lt} = $date->add(days => 1);
148 return ($key, $value);
151 sub _dispatch_custom_filters {
152 my ($class, $with_objects, $key, $value) = @_;
154 # the key should by now have no filters left
155 # if it has, catch it here:
156 die 'unrecognized filters' if $key =~ /:/;
158 my @tokens = split /\./, $key;
159 my $curr_class = $class->object_class;
161 # our key will consist of dot-delimited tokens
162 # like this: order.part.unit.name
163 # each of these tokens except the last one is one of:
164 # - a relationship in the parent object
167 # the last token must be
169 # - a column in the parent object
171 # find first token which is not a relationship,
172 # so we can pass the rest on
174 while ($i < $#tokens) {
176 $curr_class = $curr_class->meta->relationship($tokens[$i])->class;
183 my $manager = $curr_class->meta->convention_manager->auto_manager_class_name;
184 my $obj_path = join '.', @tokens[0..$i-1];
185 my $obj_prefix = join '.', @tokens[0..$i-1], '';
186 my $key_token = $tokens[$i];
187 my @additional_tokens = @tokens[$i+1..$#tokens];
189 if ($manager->can('filter')) {
190 ($key, $value, my $obj) = $manager->filter($key_token, $value, $obj_prefix, $obj_path, @additional_tokens);
191 _add_uniq($with_objects, $obj) if $obj;
193 _add_uniq($with_objects, $obj_path) if $obj_path;
196 return ($key, $value);
200 my ($array, $what) = @_;
203 @$array = (uniq @$array, listify($what));
206 sub _collapse_indirect_filters {
207 my ($flattened) = @_;
209 die 'flattened filter array length is uneven, should be possible to use as hash' if @$flattened % 2;
211 my (%keys_to_delete, %keys_to_move, @collapsed);
213 # search keys matching /::$/;
214 for (my $i = 0; $i < scalar @$flattened; $i += 2) {
215 my ($key, $value) = ($flattened->[$i], $flattened->[$i+1]);
217 next unless $key =~ /^(.*\b)::$/;
219 $keys_to_delete{$key}++;
220 $keys_to_move{$1} = $1 . '::' . $value;
223 for (my $i = 0; $i < scalar @$flattened; $i += 2) {
224 my ($key, $value) = ($flattened->[$i], $flattened->[$i+1]);
226 if ($keys_to_move{$key}) {
227 push @collapsed, $keys_to_move{$key}, $value;
230 if (!$keys_to_delete{$key}) {
231 push @collapsed, $key, $value;
239 join '.', grep $_, @_;
243 my ($value, $name, $filters) = @_;
244 return $value unless $name && $filters->{$name};
245 return [ map { _apply($_, $name, $filters) } @$value ] if 'ARRAY' eq ref $value;
246 return $filters->{$name}->($value);
255 SL::Controller::Helper::ParseFilter - Convert a form filter spec into a RDBO get_all filter
259 use SL::Controller::Helper::ParseFilter;
260 SL::DB::Object->get_all(parse_filter($::form->{filter}));
263 SL::DB::Object->get_all(parse_filter($::form->{filter},
264 with_objects => [ qw(part customer) ]));
268 A search filter will usually search for things in relations of the actual
269 search target. A search for sales orders may be filtered by the name of the
270 customer. L<Rose::DB::Object> allows you to search for these by filtering them prefixed with their table:
273 'customer.name' => 'John Doe',
274 'department.description' => [ ilike => '%Sales%' ],
275 'orddate' => [ lt => DateTime->today ],
278 Unfortunately, if you specify them in your form as these strings, the form
279 parser will convert them into nested structures like this:
289 And the substring match requires you to recognize the ilike, and modify the value.
291 C<parse_filter> tries to ease this by recognizing these structures and
292 providing suffixes for common search patterns.
298 =item C<parse_filter \%FILTER, [ %PARAMS ]>
300 First argument is the filter from form. It is highly recommended that you put
301 all filter attributes into a named container as to not confuse them with the
304 Nested structures will be parsed and interpreted as foreign references. For
305 example if you search for L<Order>s, this input will search for those with a
306 specific L<Salesman>:
308 [% L.select_tag('filter.salesman.id', ...) %]
310 Additionally you can add a modifier to the name to set a certain method:
312 [% L.input_tag('filter.department.description:substr::ilike', ...) %]
314 This will add the "% .. %" wildcards for substr matching in SQL, and add an
315 C<< ilike => $value >> block around it to match case insensitively.
317 As a rule all value filters require a single colon and must be placed before
318 match method suffixes, which are appended with 2 colons. See below for a full
325 Unfortunately Template cannot parse the postfixes if you want to
326 rerender the filter. For this reason all colon filter keys are by
327 default laundered into underscores, so you can use them like this:
329 [% L.input_tag('filter.price:number::lt', filter.price_number__lt) %]
331 Also Template has trouble when looking up the contents of arrays, so
332 these will get copied into a _ suffixed version as hashes:
334 [% L.checkbox_tag('filter.ids[]', value=15, checked=filter.ids_.15) %]
336 All of your original entries will stay intact. If you don't want this to
337 happen pass C<< no_launder => 1 >> as a parameter. Additionally you can pass a
338 different target for the laundered values with the C<launder_to> parameter. It
339 takes a hashref and will deep copy all values in your filter to the target. So
340 if you have a filter that looks like this:
343 'price:number::lt' => '2,30',
345 type => [ 'part', 'assembly' ],
350 parse_filter($filter, launder_to => $laundered_filter = { })
352 the original filter will be unchanged, and C<$laundered_filter> will end up
356 'price_number__lt' => '2,30',
358 'type_' => { part => 1, assembly => 1 },
361 =head1 INDIRECT FILTER METHODS
363 The reason for the method being last is that it is possible to specify the
364 method in another input. Suppose you want a date input and a separate
365 before/after/equal select, you can use the following:
367 [% L.date_tag('filter.appointed_date:date', ... ) %]
371 [% L.select_tag('filter.appointed_date:date::', ... ) %]
373 The special empty method will be used to set the method for the previous
376 =head1 CUSTOM FILTERS FROM OBJECTS
378 If the L<parse_filter> call contains a parameter C<class>, custom filters will
379 be honored. Suppose you have added a custom filter 'all' for parts which
380 expands to search both description and partnumber, the following
383 'part.all:substr::ilike' => 'A1',
390 part.description => { ilike => '%A1%' },
391 part.partnumber => { ilike => '%A1%' },
395 For more about custom filters, see L<SL::DB::Helper::Filtered>.
397 =head1 FILTERS (leading with :)
399 The following filters are built in, and can be used.
405 Parses the input string with C<< DateTime->from_lxoffice >>
409 Pasres the input string with C<< Form->parse_amount >>
413 Parses the input string with C<< Form->parse_amount / 100 >>
417 Adds "%" at the end of the string.
421 Adds "%" at the end of the string.
425 Adds "% .. %" around the search string.
427 =item eq_ignore_empty
429 Ignores this item if it's empty. Otherwise compares it with the
430 standard SQL C<=> operator.
434 =head2 METHODS (leading with ::)
446 All these are recognized like the L<Rose::DB::Object> methods.
450 =head1 BUGS AND CAVEATS
452 This will not properly handle multiple versions of the same object in different
455 Suppose you want all L<SL::DB::Order>s which have either themselves a certain
456 customer, or are linked to a L<SL::DB::Invoice> with this customer, the
457 following will not work as you expect:
460 L.input_tag('customer.name:substr::ilike', ...)
461 L.input_tag('invoice.customer.name:substr::ilike', ...)
463 This will search for orders whose invoice has the _same_ customer, which matches
464 both inputs. This is because tables are aliased by their name and not by their
465 position in with_objects.
473 Additional filters should be pluggable.
479 Sven Schöling E<lt>s.schoeling@linet-services.deE<gt>