Re: last 10 ... next 10?

[knobunc]

If your database does not support LIMIT or an equivalent to allow you to retrieve the appropriate number of rows at an offset then you can do the following:

# offset is how many records into the dataset to start,
#     0 means start at the beginning
# count is how many records to read
# This returns an arrayref to an arrayref corresponding
# to the values for each row.  You will only get at most
# count rows returned, but may get less if there is less
# data than requested
sub get_data_from_db {
    my ($offset, $count, $dbh) = @_;

    my $sth = $dbh->prepare(
    qq(SELECT whatever
       FROM TABLE_FOO
       ));
    $sth->execute;

    my @results;
    while ($row = $sth->fetchrow_arrayref and $count-- > 0) {
        if ($offset > 0) {
            $offset--;
        $next;
        }
        push @results, $row;
    }
    $sth->finish;

    return \@results;
}

Below this is a lot of Mason specific blabbering. If you feel like decoding my feverish screed about how I hook the above up to the HTML that the user sees then keep going, otherwise there is probably not much else.

Since I am using Mason I wrote a component to handle tables that I pass the parameters for the number of rows to display and the offset to start at and then generate widgets in the html to allow the user to move forward or backward through the dataset generating the correct URL with the appropriate offset and count plugged in. The component is smart enough to know when it has hit the beginning and does not put up a widget to allow you to scroll further backwards, and similarly at the end. To detect the end, it actually cheats, I request one more row than I want and the table component uses that ti detect if there is more data. The table component does not display the additional marker row.

In order to allow movement any page that wants to support this has to have 2 page arguments that match the ones that the table component knows how to generate so that it can run the correct subroutine that executes the DB query and pass in the appropriate offset and count parameters.

I actually added a further wrinkle by allowing users to click on the column headings in the tables to sort the rows (or reverse sort if they have already sorted by that row). In order to do this I added a third parameter for sorting and the table component generates little up/down arrows to show which column is sorted. The table component then generates link tags for each column heading that tells the page what the appropriate sort is. This then gets passed into the query which validates the sort and turns it into a SQL order by clause. The following is the full blown beast.

sub get_top_status {
    my %args =
        (start_point => 1,
         num_rows    => 10,
         sort_by     => ['result ascending'],
         @_);
    my $dbh = $args{dbh} or croak 'Missing DB handle';

    # Define the valid sorts
    my %sorts =
        (id    => 'T.id',
         name  => 'T.name'
    );

    # Build the SQL sort string
    my $sort = build_sort($args{sort_by}, \%sorts, ['name']);

    my $statement = 
    "SELECT name, id, foo FROM FOO_TABLE ORDER BY $sort";
    my $sth = $dbh->prepare($statement);
    $sth->execute();

    my @results;
    while ($row = $sth->fetchrow_arrayref and $count-- > 0) {
        if ($offset > 0) {
            $offset--;
            $next;
        }
        push @results, $row;
    }
    $sth->finish;

    return \@results;
}

# Takes a sort order and a hash defining the legal sorts and produces
# a string for embedding in SQL statements
#  - Order is an arrayref of all the sorts to use
#  - Sorts is a hashref keyed by the sort names mapping sorts to colum
+ns
#  - Fallback is an optional arrayref of the columns to append to ensu
+re 
#    the ordering is sane (if we are sorting by severity we still want
+ to
#    sort by name where the severity is the same)
sub build_sort {
    my ($order, $sorts, $fallback) = @_;
    $fallback = [] unless defined $fallback;
    my %done = ();

    my $sort = '';
    foreach my $s (@$order, @$fallback) {
        # Skip ones we have already seen
        next if exists $done{$s};
        $done{$s} = 1;

        # Build the sort string
        $sort .= ', ' unless $sort eq '';
        my ($col, $order) = (split(/ /, $s), 'asc');

        croak "Bad sort request '$s' (parsed '$col' '$order')."
            unless exists $sorts->{$col};

        $sort .= "$sorts->{$col} $order";
    }

    return $sort;
}