1 package SL::DB::Helper::ActsAsList;
5 use parent qw(Exporter);
6 our @EXPORT = qw(move_position_up move_position_down add_to_list remove_from_list reorder_list configure_acts_as_list
7 get_previous_in_list get_next_in_list);
14 my ($class, @params) = @_;
15 my $importing = caller();
17 $importing->before_save( sub { SL::DB::Helper::ActsAsList::set_position(@_) });
18 $importing->before_delete(sub { SL::DB::Helper::ActsAsList::remove_position(@_) });
20 # Use 'goto' so that Exporter knows which module to import into via
22 goto &Exporter::import;
29 sub move_position_up {
34 sub move_position_down {
36 do_move($self, 'down');
39 sub remove_from_list {
43 remove_position($self);
45 # Set to -1 manually because $self->update_attributes() would
46 # trigger the before_save() hook from this very plugin assigning a
47 # number at the end of the list again.
48 my $table = $self->meta->table;
49 my $column = column_name($self);
50 my $primary_key_col = ($self->meta->primary_key)[0];
54 WHERE ${primary_key_col} = ?
56 $self->db->dbh->do($sql, undef, $self->$primary_key_col);
57 $self->$column(undef);
60 return $self->db->in_transaction ? $worker->() : $self->db->do_transaction($worker);
64 my ($self, %params) = @_;
66 croak "Invalid parameter 'position'" unless ($params{position} || '') =~ m/^ (?: before | after | first | last ) $/x;
68 if ($params{position} eq 'last') {
74 my $table = $self->meta->table;
75 my $primary_key_col = ($self->meta->primary_key)[0];
76 my $column = column_name($self);
77 my ($group_by, @values) = get_group_by_where($self);
78 $group_by = " AND ${group_by}" if $group_by;
81 if ($params{position} eq 'first') {
85 # Can only be 'before' or 'after' -- 'last' has been checked above
88 my $reference = $params{reference};
89 croak "Missing parameter 'reference'" if !$reference;
93 $reference_pos = $reference->$column;
95 ($reference_pos) = $self->db->dbh->selectrow_array(qq|SELECT ${column} FROM ${table} WHERE ${primary_key_col} = ?|, undef, $reference);
98 $new_position = $params{position} eq 'before' ? $reference_pos : $reference_pos + 1;
103 SET ${column} = ${column} + 1
104 WHERE (${column} > ?)
109 $self->db->dbh->do($query, undef, $new_position - 1, @values);
110 $self->update_attributes($column => $new_position);
113 return $self->db->in_transaction ? $worker->() : $self->db->do_transaction($worker);
116 sub get_next_in_list {
118 return get_previous_or_next($self, 'next');
121 sub get_previous_in_list {
123 return get_previous_or_next($self, 'previous');
127 my ($class_or_self, @ids) = @_;
129 return 1 unless @ids;
131 my $self = ref($class_or_self) ? $class_or_self : $class_or_self->new;
132 my $column = column_name($self);
133 my $result = $self->db->do_transaction(sub {
134 my $query = qq|UPDATE | . $self->meta->table . qq| SET ${column} = ? WHERE id = ?|;
135 my $sth = $self->db->dbh->prepare($query) || die $self->db->dbh->errstr;
137 foreach my $new_position (1 .. scalar(@ids)) {
138 $sth->execute($new_position, $ids[$new_position - 1]) || die $sth->errstr;
147 sub configure_acts_as_list {
148 my ($class, %params) = @_;
150 $list_spec{$class} = {
151 group_by => $params{group_by},
152 column_name => $params{column_name},
160 sub get_group_by_where {
163 my $group_by = get_spec(ref $self, 'group_by') || [];
164 $group_by = [ $group_by ] if $group_by && !ref $group_by;
166 my (@where, @values);
167 foreach my $column (@{ $group_by }) {
168 my $value = $self->$column;
169 push @values, $value if defined $value;
170 push @where, defined($value) ? "(${column} = ?)" : "(${column} IS NULL)";
173 return (join(' AND ', @where), @values);
178 my $column = column_name($self);
179 my $value = $self->$column;
181 return 1 if defined($value) && ($value != -1);
183 my $table = $self->meta->table;
184 my ($group_by, @values) = get_group_by_where($self);
185 $group_by = " AND ${group_by}" if $group_by;
187 SELECT COALESCE(MAX(${column}), 0)
189 WHERE (${column} <> -1)
193 my $max_position = $self->db->dbh->selectrow_arrayref($sql, undef, @values)->[0];
194 $self->$column($max_position + 1);
199 sub remove_position {
201 my $column = column_name($self);
204 my $value = $self->$column;
205 return 1 unless defined($value) && ($value != -1);
207 my $table = $self->meta->table;
208 my ($group_by, @values) = get_group_by_where($self);
209 $group_by = ' AND ' . $group_by if $group_by;
212 SET ${column} = ${column} - 1
213 WHERE (${column} > ?)
217 $self->db->dbh->do($sql, undef, $value, @values);
223 my ($self, $direction) = @_;
225 croak "Object has not been saved yet" unless $self->id;
227 my $column = column_name($self);
228 my $old_position = $self->$column;
229 croak "No position set yet" unless defined($old_position) && ($old_position != -1);
231 my $table = $self->meta->table;
232 my ($comp_sel, $comp_upd, $min_max, $plus_minus) = $direction eq 'up' ? ('<', '>=', 'MAX', '+') : ('>', '<=', 'MIN', '-');
233 my ($group_by, @values) = get_group_by_where($self);
234 $group_by = ' AND ' . $group_by if $group_by;
236 SELECT ${min_max}(${column})
238 WHERE (${column} <> -1)
239 AND (${column} ${comp_sel} ?)
243 my $new_position = $self->db->dbh->selectrow_arrayref($sql, undef, $old_position, @values)->[0];
245 return undef unless defined $new_position;
250 WHERE (${column} = ?)
254 $self->db->dbh->do($sql, undef, $old_position, $new_position, @values);
256 $self->update_attributes($column => $new_position);
259 sub get_previous_or_next {
260 my ($self, $direction) = @_;
262 my $asc_desc = $direction eq 'next' ? 'ASC' : 'DESC';
263 my $comparator = $direction eq 'next' ? '>' : '<';
264 my $table = $self->meta->table;
265 my $column = column_name($self);
266 my $primary_key_col = ($self->meta->primary_key)[0];
267 my ($group_by, @values) = get_group_by_where($self);
268 $group_by = " AND ${group_by}" if $group_by;
270 SELECT ${primary_key_col}
272 WHERE (${column} ${comparator} ?)
274 ORDER BY ${column} ${asc_desc}
278 my $id = ($self->db->dbh->selectrow_arrayref($sql, undef, $self->$column, @values) || [])->[0];
280 return $id ? $self->_get_manager_class->find_by(id => $id) : undef;
285 my $column = get_spec(ref $self, 'column_name');
286 return $column if $column;
287 return $self->can('sortkey') ? 'sortkey' : 'position';
291 my ($class, $key) = @_;
293 return undef unless $list_spec{$class};
294 return $list_spec{$class}->{$key};
306 SL::DB::Helper::ActsAsList - Mixin for managing ordered items by a
311 package SL::DB::SomeObject;
312 use SL::DB::Helper::ActsAsList;
314 package SL::Controller::SomeController;
316 # Assign a position automatically
317 $obj = SL::DB::SomeObject->new(description => 'bla');
320 # Move items up and down
321 $obj = SL::DB::SomeOBject->new(id => 1)->load;
322 $obj->move_position_up;
323 $obj->move_position_down;
325 # Adjust all remaining positions automatically
328 This mixin assumes that the mixing package's table contains a column
329 called C<position> or C<sortkey> (for legacy tables). This column is
330 set automatically upon saving the object if it hasn't been set
331 already. If it hasn't then it will be set to the maximum position used
332 in the table plus one.
334 When the object is deleted all positions greater than the object's old
335 position are decreased by one.
337 The column name to use can be configured via L<configure_acts_as_list>.
339 =head1 CLASS FUNCTIONS
343 =item C<configure_acts_as_list %params>
345 Configures the mixin's behaviour. C<%params> can contain the following
352 The name of the column containing the position. If not set explicitly
353 then the mixin will use C<sortkey> if the model contains such a column
354 (only for legacy tables) and C<position> otherwise.
358 An optional column name (or array reference of column names) by which
359 to group. If a table contains items for several distinct sets and each
360 set has its own sorting then this can be used.
362 An example would be requirement spec text blocks. They have a column
363 called C<output_position> that selects where to output the text blocks
364 (either before or after the sections). Furthermore these text blocks
365 each belong to a single requirement spec document. So each combination
366 of C<requirement_spec_id> and C<output_position> should have its own
367 set of C<position> values, which can be achieved by configuring this
368 mixin with C<group_by = [qw(requirement_spec_id output_position)]>.
374 =head1 INSTANCE FUNCTIONS
378 =item C<move_position_up>
380 Swaps the object with the object one step above the current one
381 regarding their sort order by exchanging their C<position> values.
383 =item C<move_position_down>
385 Swaps the object with the object one step below the current one
386 regarding their sort order by exchanging their C<position> values.
388 =item C<add_to_list %params>
390 Adds this item to the list. The parameter C<position> is required and
391 can be one of C<first>, C<last>, C<before> and C<after>. With C<first>
392 the item is inserted as the first item in the list and all other
393 item's positions are shifted up by one. For C<position = last> the
394 item is inserted at the end of the list.
396 For C<before> and C<after> an additional parameter C<reference> is
397 required. This is either a Rose model instance or the primary key of
398 one. The current item will then be inserted either before or after the
399 referenced item by shifting all the appropriate item positions up by
402 After this function C<$self>'s positional column has been set and
403 saved to the database.
405 =item C<remove_from_list>
407 Sets this items positional column to C<-1>, saves it and moves all
408 following items up by 1.
410 =item C<get_previous_in_list>
412 Fetches the previous item in the list. Returns C<undef> if C<$self> is
413 already the first one.
415 =item C<get_next_in_list>
417 Fetches the next item in the list. Returns C<undef> if C<$self> is
418 already the last one.
420 =item C<reorder_list @ids>
422 Re-orders the objects given in C<@ids> by their position in C<@ids> by
423 updating all of their positional columns. Each element in
424 C<@positions> must be the ID of an object. The new position is the
425 ID's index inside C<@ids> plus one (meaning the first element's new
426 position will be 1 and not 0).
428 This works by executing SQL "UPDATE" statements directly.
430 Returns the result of the whole transaction (trueish in case of
433 This method can be called both as a class method or an instance
444 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>