From dd2cd96b9396e8fc63c5bd4ee43d88c77923dc17 Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Thu, 30 Dec 2010 16:43:06 +0100 Subject: [PATCH] =?utf8?q?Helfer=20f=C3=BCr=20Manager=20f=C3=BCr=20das=20S?= =?utf8?q?ortieren=20von=20Datenbankergebnissen?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- SL/DB/Helpers/Sorted.pm | 177 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 177 insertions(+) create mode 100644 SL/DB/Helpers/Sorted.pm diff --git a/SL/DB/Helpers/Sorted.pm b/SL/DB/Helpers/Sorted.pm new file mode 100644 index 000000000..34b01ba09 --- /dev/null +++ b/SL/DB/Helpers/Sorted.pm @@ -0,0 +1,177 @@ +package SL::DB::Helpers::Sorted; + +use strict; + +require Exporter; +our @ISA = qw(Exporter); +our @EXPORT = qw(get_all_sorted make_sort_string); + +my %sort_spec; + +sub make_sort_string { + my ($class, %params) = @_; + + %sort_spec = $class->_sort_spec unless %sort_spec; + + my $sort_dir = defined($params{sort_dir}) ? $params{sort_dir} * 1 : $sort_spec{default}->[1]; + my $sort_dir_str = $sort_dir ? 'ASC' : 'DESC'; + + my $sort_by = $params{sort_by}; + $sort_by = $sort_spec{default}->[0] unless $sort_spec{columns}->{$sort_by}; + + my $nulls_str = ''; + if ($sort_spec{nulls}) { + $nulls_str = ref($sort_spec{nulls}) ? ($sort_spec{nulls}->{$sort_by} || $sort_spec{nulls}->{default}) : $sort_spec{nulls}; + $nulls_str = " NULLS ${nulls_str}" if $nulls_str; + } + + my $sort_by_str = $sort_spec{columns}->{$sort_by}; + $sort_by_str = [ $sort_by_str ] unless ref($sort_by_str) eq 'ARRAY'; + $sort_by_str = join(', ', map { "${_} ${sort_dir_str}${nulls_str}" } @{ $sort_by_str }); + + return wantarray ? ($sort_by, $sort_dir, $sort_by_str) : $sort_by_str; +} + +sub get_all_sorted { + my ($class, %params) = @_; + my $sort_str = $class->make_sort_string(sort_by => delete($params{sort_by}), sort_dir => delete($params{sort_dir})); + + return $class->get_all(sort_by => $sort_str, %params); +} + +1; + +__END__ + +=encoding utf8 + +=head1 NAME + +SL::DB::Helpers::Sorted - Mixin for a manager class that handles +sorting of database records + +=head1 SYNOPSIS + + package SL::DB::Manager::Message; + + use SL::DB::Helpers::Sorted; + + sub _sort_spec { + return ( columns => { recipient_id => [ 'CASE + WHEN recipient_group_id IS NULL THEN lower(recipient.name) + ELSE lower(recipient_group.name) + END', ], + sender_id => [ 'lower(sender.name)', ], + created_at => [ 'created_at', ], + subject => [ 'lower(subject)', ], + status => [ 'NOT COALESCE(unread, FALSE)', 'created_at' ], + }, + default => [ 'status', 1 ], + nulls => { default => 'LAST', + subject => 'FIRST', + } + ); + } + + package SL::Controller::Message; + + sub action_list { + my $messages = SL::DB::Manager::Message->get_all_sorted(sort_by => $::form->{sort_by}, + sort_dir => $::form->{sort_dir}); + } + +=head1 CLASS FUNCTIONS + +=over 4 + +=item C + +Evaluates C<$params{sort_by}> and C<$params{sort_dir}> and returns an +SQL string suitable for sorting. The package this package is mixed +into has to provide a method L that returns a hash whose +structure is explained below. That hash is authoritive in which +columns may be sorted, which column to sort by by default and how to +handle C values. + +Returns the SQL string in scalar context. In array context it returns +three values: the actual column it sorts by (suitable for another call +to L), the actual sort direction (either 0 or 1) +and the SQL string. + +=item C + +Returns C<< $class->get_all >> with C set to the value +returned by c<< $class->make_sort_string(%params) >>. + +=back + +=head1 CLASS FUNCTIONS PROVIDED BY THE MIXING PACKAGE + +=over 4 + +=item C<_sort_spec> + +This method is actually not part of this package but must be provided +by the package this helper is mixed into. + +Returns a has with the following keys: + +=over 2 + +=item C + +A two-element array containing the name and direction by which to sort +in default cases. Example: + + default => [ 'name', 1 ], + +=item C + +A hash reference. Its keys are column names, and its values are SQL +strings by which to sort. Example: + + columns => { transaction_description => 'oe.transaction_description', + customer_name => 'lower(customer.name)', + }, + +If sorting by a column is requested that is not a key in this hash +then the default column name will be used. + +The value can be either a scalar or an array reference. If it's the +latter then both the sort direction as well as the null handling will +be appended to each of its members. + +=item C + +Either a scalar or a hash reference determining where C values +will be sorted. If undefined then the decision is left to the +database. + +If it is a scalar then all the same value will be used for all +classes. The value is either C or C. + +If it is a hash reference then its keys are column names (not SQL +names). The values are either C or C. If a column name is +not found in this hash then the special keu C will be looked +up and used if it is found. + +Example: + + nulls => { transaction_description => 'FIRST', + customer_name => 'FIRST', + default => 'LAST', + }, + +=back + +=back + +=head1 BUGS + +Nothing here yet. + +=head1 AUTHOR + +Moritz Bunkus Em.bunkus@linet-services.deE + +=cut -- 2.20.1