projekt kivitendo

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<make_sort_string %params>

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</_sort_spec> 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<NULL> 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</make_sort_string>), the actual sort direction (either 0 or 1)

and the SQL string.

=item C<get_all_sorted %params>

Returns C<< $class->get_all >> with C<sort_by> 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<default>

A two-element array containing the name and direction by which to sort

in default cases. Example:

  default => [ 'name', 1 ],

=item C<columns>

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<nulls>

Either a scalar or a hash reference determining where C<NULL> 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<FIRST> or C<LAST>.

If it is a hash reference then its keys are column names (not SQL

names). The values are either C<FIRST> or C<LAST>. If a column name is

not found in this hash then the special keu C<default> 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 E<lt>m.bunkus@linet-services.deE<gt>

=cut