In writing REST style interfaces, we would like to minimize code and increase transparancy by passing sort, limit and offset parameters from the HTTP request into your SQL queries.

Note that SQL::Abstract::More throws an error if you specify -offset without -limit, so I always include my own defaults for those instead of relying on the database.

# In your model use List::Util qw(pairmap); use SQL::Abstract::More; my $sqla = SQL::Abstract::More->new; sub _query_params { my ($self, @params) = @_; # Select request query parameters, skipping any omitted. # Mogrify keys from e.g., 'offset' → '-offset' as per # SQL::Abstract::More. Any non-word characters in the # parameter's value are excised to prevent injection # attacks. When building hash, provide default values (using # -offset style) before calling this. map { my $value = $self->param($_) =~ s/\W/ /gr; defined $value ? pairmap { ('-'.$a) => $b } ( $_, $value) : () # empty list skips } @params; } # Example model method sub get_something { my ($self) = @_; # $self passed from your Controller … my %qp = ( -offset => 10, -limit => 3, $self->_query_params(qw(offset limit order_by)) ); my ($sql, @bind) = $sqla->select(-from => 'mytable', %qp ); … }

Here is a complete example: