use strict;
use parent qw(Exporter);
-our @EXPORT = qw(move_position_up move_position_down reorder_list);
+our @EXPORT = qw(move_position_up move_position_down reorder_list configure_acts_as_list);
use Carp;
+my %list_spec;
+
sub import {
my ($class, @params) = @_;
my $importing = caller();
return $result;
}
+sub configure_acts_as_list {
+ my ($class, %params) = @_;
+
+ $list_spec{$class} = {
+ group_by => $params{group_by},
+ column_name => $params{column_name},
+ };
+}
+
#
# Helper functions
#
+sub get_group_by_where {
+ my ($self) = @_;
+
+ my $group_by = get_spec(ref $self, 'group_by') || [];
+ $group_by = [ $group_by ] if $group_by && !ref $group_by;
+
+ my @where = map { my $value = $self->$_; defined($value) ? "(${_} = " . $value . ")" : "(${_} IS NULL)" } @{ $group_by };
+
+ return join ' AND ', @where;
+}
+
sub set_position {
my ($self) = @_;
my $column = column_name($self);
- if (!defined $self->$column) {
- my $max_position = $self->db->dbh->selectrow_arrayref(qq|SELECT COALESCE(max(${column}), 0) FROM | . $self->meta->table)->[0];
- $self->$column($max_position + 1);
- }
+ return 1 if defined $self->$column;
+
+ my $table = $self->meta->table;
+ my $where = get_group_by_where($self);
+ $where = " WHERE ${where}" if $where;
+ my $sql = <<SQL;
+ SELECT COALESCE(max(${column}), 0)
+ FROM ${table}
+ ${where}
+SQL
+
+ my $max_position = $self->db->dbh->selectrow_arrayref($sql)->[0];
+ $self->$column($max_position + 1);
return 1;
}
my $column = column_name($self);
$self->load;
- if (defined $self->$column) {
- $self->_get_manager_class->update_all(set => { $column => \"${column} - 1" },
- where => [ $column => { gt => $self->$column } ]);
- }
+ return 1 unless defined $self->$column;
+
+ my $table = $self->meta->table;
+ my $value = $self->$column;
+ my $group_by = get_group_by_where($self);
+ $group_by = ' AND ' . $group_by if $group_by;
+ my $sql = <<SQL;
+ UPDATE ${table}
+ SET ${column} = ${column} - 1
+ WHERE (${column} > ${value}) ${group_by}
+SQL
+
+ $self->db->dbh->do($sql);
return 1;
}
croak "Object has not been saved yet" unless $self->id;
croak "No position set yet" unless defined $self->$column;
- my ($comp_sql, $comp_rdbo, $min_max, $plus_minus) = $direction eq 'up' ? ('<', 'ge', 'max', '+') : ('>', 'le', 'min', '-');
+ my $table = $self->meta->table;
+ my $old_position = $self->$column;
+ my ($comp_sel, $comp_upd, $min_max, $plus_minus) = $direction eq 'up' ? ('<', '>=', 'max', '+') : ('>', '<=', 'min', '-');
+ my $group_by = get_group_by_where($self);
+ $group_by = ' AND ' . $group_by if $group_by;
+ my $sql = <<SQL;
+ SELECT ${min_max}(${column})
+ FROM ${table}
+ WHERE (${column} ${comp_sel} ${old_position})
+ ${group_by}
+SQL
- my $new_position = $self->db->dbh->selectrow_arrayref(qq|SELECT ${min_max}(${column}) FROM | . $self->meta->table . qq| WHERE ${column} ${comp_sql} | . $self->$column)->[0];
+ my $new_position = $self->db->dbh->selectrow_arrayref($sql)->[0];
return undef unless defined $new_position;
- $self->_get_manager_class->update_all(set => { $column => $self->$column },
- where => [ $column => $new_position ]);
+ $sql = <<SQL;
+ UPDATE ${table}
+ SET ${column} = ${old_position}
+ WHERE (${column} = ${new_position})
+ ${group_by};
+SQL
+
+ $self->db->dbh->do($sql);
+
$self->update_attributes($column => $new_position);
}
sub column_name {
my ($self) = @_;
+ my $column = get_spec(ref $self, 'column_name');
+ return $column if $column;
return $self->can('sortkey') ? 'sortkey' : 'position';
}
+sub get_spec {
+ my ($class, $key) = @_;
+
+ return undef unless $list_spec{$class};
+ return $list_spec{$class}->{$key};
+}
+
1;
__END__
=head1 NAME
SL::DB::Helper::ActsAsList - Mixin for managing ordered items by a
-column I<position> or I<sortkey>
+column
=head1 SYNOPSIS
When the object is deleted all positions greater than the object's old
position are decreased by one.
-=head1 FUNCTIONS
+The column name to use can be configured via L<configure_acts_as_list>.
+
+=head1 CLASS FUNCTIONS
+
+=over 4
+
+=item C<configure_acts_as_list %params>
+
+Configures the mixin's behaviour. C<%params> can contain the following
+values:
+
+=over 2
+
+=item C<column_name>
+
+The name of the column containing the position. If not set explicitly
+then the mixin will use C<sortkey> if the model contains such a column
+(only for legacy tables) and C<position> otherwise.
+
+=item C<group_by>
+
+An optional column name (or array reference of column names) by which
+to group. If a table contains items for several distinct sets and each
+set has its own sorting then this can be used.
+
+An example would be requirement spec text blocks. They have a column
+called C<output_position> that selects where to output the text blocks
+(either before or after the sections). Furthermore these text blocks
+each belong to a single requirement spec document. So each combination
+of C<requirement_spec_id> and C<output_position> should have its own
+set of C<position> values, which can be achieved by configuring this
+mixin with C<group_by = [qw(requirement_spec_id output_position)]>.
+
+=back
+
+=back
+
+=head1 INSTANCE FUNCTIONS
=over 4
projects / kivitendo-erp.git / blobdiff