1 package SL::DB::Helpers::Sorted;
6 our @ISA = qw(Exporter);
7 our @EXPORT = qw(get_all_sorted make_sort_string);
11 sub make_sort_string {
12 my ($class, %params) = @_;
14 _make_sort_spec($class) unless %sort_spec;
16 my $sort_dir = defined($params{sort_dir}) ? $params{sort_dir} * 1 : $sort_spec{default}->[1];
17 my $sort_dir_str = $sort_dir ? 'ASC' : 'DESC';
19 my $sort_by = $params{sort_by};
20 $sort_by = $sort_spec{default}->[0] unless $sort_spec{columns}->{$sort_by};
23 if ($sort_spec{nulls}) {
24 $nulls_str = ref($sort_spec{nulls}) ? ($sort_spec{nulls}->{$sort_by} || $sort_spec{nulls}->{default}) : $sort_spec{nulls};
25 $nulls_str = " NULLS ${nulls_str}" if $nulls_str;
28 my $sort_by_str = $sort_spec{columns}->{$sort_by};
29 $sort_by_str = [ $sort_by_str ] unless ref($sort_by_str) eq 'ARRAY';
30 $sort_by_str = join(', ', map { "${_} ${sort_dir_str}${nulls_str}" } @{ $sort_by_str });
32 return wantarray ? ($sort_by, $sort_dir, $sort_by_str) : $sort_by_str;
36 my ($class, %params) = @_;
37 my $sort_str = $class->make_sort_string(sort_by => delete($params{sort_by}), sort_dir => delete($params{sort_dir}));
39 return $class->get_all(sort_by => $sort_str, %params);
45 %sort_spec = $class->_sort_spec if defined &{ "${class}::_sort_spec" };
47 my $meta = $class->object_class->meta;
49 if (!$sort_spec{default}) {
50 my @primary_keys = $meta->primary_key;
51 $sort_spec{default} = [ "" . $primary_keys[0], 1 ];
54 $sort_spec{columns} ||= { SIMPLE => [ map { "$_" } $meta->columns ] };
56 if ($sort_spec{columns}->{SIMPLE}) {
57 my $table = $meta->table;
59 if (!ref($sort_spec{columns}->{SIMPLE}) && ($sort_spec{columns}->{SIMPLE} eq 'ALL')) {
60 map { $sort_spec{columns}->{"$_"} ||= "${table}.${_}"} @{ $meta->columns };
61 delete $sort_spec{columns}->{SIMPLE};
63 map { $sort_spec{columns}->{$_} = "${table}.${_}" } @{ delete($sort_spec{columns}->{SIMPLE}) };
76 SL::DB::Helpers::Sorted - Mixin for a manager class that handles
77 sorting of database records
81 package SL::DB::Manager::Message;
83 use SL::DB::Helpers::Sorted;
86 return ( columns => { recipient_id => [ 'CASE
87 WHEN recipient_group_id IS NULL THEN lower(recipient.name)
88 ELSE lower(recipient_group.name)
90 sender_id => [ 'lower(sender.name)', ],
91 created_at => [ 'created_at', ],
92 subject => [ 'lower(subject)', ],
93 status => [ 'NOT COALESCE(unread, FALSE)', 'created_at' ],
95 default => [ 'status', 1 ],
96 nulls => { default => 'LAST',
102 package SL::Controller::Message;
105 my $messages = SL::DB::Manager::Message->get_all_sorted(sort_by => $::form->{sort_by},
106 sort_dir => $::form->{sort_dir});
109 =head1 CLASS FUNCTIONS
113 =item C<make_sort_string %params>
115 Evaluates C<$params{sort_by}> and C<$params{sort_dir}> and returns an
116 SQL string suitable for sorting. The package this package is mixed
117 into has to provide a method L</_sort_spec> that returns a hash whose
118 structure is explained below. That hash is authoritive in which
119 columns may be sorted, which column to sort by by default and how to
120 handle C<NULL> values.
122 Returns the SQL string in scalar context. In array context it returns
123 three values: the actual column it sorts by (suitable for another call
124 to L</make_sort_string>), the actual sort direction (either 0 or 1)
127 =item C<get_all_sorted %params>
129 Returns C<< $class->get_all >> with C<sort_by> set to the value
130 returned by c<< $class->make_sort_string(%params) >>.
134 =head1 CLASS FUNCTIONS PROVIDED BY THE MIXING PACKAGE
140 This method is actually not part of this package but can be provided
141 by the package this helper is mixed into. If it isn't then all columns
142 of the corresponding table (as returned by the model's meta data) will
143 be eligible for sorting.
145 Returns a hash with the following keys:
151 A two-element array containing the name and direction by which to sort
152 in default cases. Example:
154 default => [ 'name', 1 ],
156 Defaults to the table's primary key column (the first column if the
157 primary key is composited).
161 A hash reference. Its keys are column names, and its values are SQL
162 strings by which to sort. Example:
164 columns => { SIMPLE => [ 'transaction_description', 'orddate' ],
165 the_date => 'CASE WHEN oe.quotation THEN oe.quodate ELSE oe.orddate END',
166 customer_name => 'lower(customer.name)',
169 If sorting by a column is requested that is not a key in this hash
170 then the default column name will be used.
172 The value can be either a scalar or an array reference. If it's the
173 latter then both the sort direction as well as the null handling will
174 be appended to each of its members.
176 The special key C<SIMPLE> can be a scalar or an array reference. If it
177 is an array reference then it contains column names that are mapped
178 1:1 onto the table's columns. If it is the scalar 'ALL' then all
179 columns in that model's meta data are mapped 1:1 unless the C<columns>
180 hash already contains a key for that column.
182 If C<columns> is missing then all columns of the model will be
183 eligible for sorting. The list of columns is looked up in the model's
188 Either a scalar or a hash reference determining where C<NULL> values
189 will be sorted. If undefined then the decision is left to the
192 If it is a scalar then all the same value will be used for all
193 classes. The value is either C<FIRST> or C<LAST>.
195 If it is a hash reference then its keys are column names (not SQL
196 names). The values are either C<FIRST> or C<LAST>. If a column name is
197 not found in this hash then the special keu C<default> will be looked
198 up and used if it is found.
202 nulls => { transaction_description => 'FIRST',
203 customer_name => 'FIRST',
217 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>