stonecolddevin has asked for the wisdom of the Perl Monks concerning the following question:

Howdy all,

I'm having some trouble with DBIx::Class. I'm trying to paginate results, 10 results per page, nothing special.

However, what i'm running into is

  1. the results aren't being ordered correctly, it's ordering them ASCendingly when i've specified DESCending results in the query and
  2. it's grouping the results (or something) so that there ARE 10 results per page, instead of giving me 10 results on one page, then the remaining 6 on the next (i'm testing with 16 records)
Instead, it gives me 10 on each page, and I can't really tell how it orders them after you click on the "next" link.

I'm honestly baffled, I don't know how this setup is supposed to work, or how to change the paged results, the docs only say:

When you expect a large number of results, you can ask DBIx::Class for a paged resultset, which will fetch only a small number of records at a time:

my $rs = $schema->resultset('Artist')->search(
undef,
{
page => 1, # page to return (defaults to 1)
rows => 10, # number of results per page
},
);

return $rs->all(); # all records for page 1

The page attribute does not have to be specified in your search:

my $rs = $schema->resultset('Artist')->search(
undef,
{
rows => 10,
}
);

return $rs->page(1); # DBIx::Class::ResultSet containing first 10 records

In either of the above cases, you can return a Data::Page object for the resultset (suitable for use in e.g. a template) using the pager method:

return $rs->pager();

return $rs->page(1); # DBIx::Class::ResultSet containing first 10 records <- this doesn't work either. Currently, I have something like this:

sub main {
    
    my $self = shift;
    my $tmpl = $self->load_tmpl(
        'index.html',
        %config_vars
    );
    
    my $q = $self->query;
    
    
    my $comments = $schema->resultset('News')->search(
        undef,
        { 
          rows => 10,
          page => $q->param('next_page'),
          order_by => 'date DESC' 
        }
    );
    
    
    ## let's populate @comments
    for ( $comments->all )  {
    
        my %row_data;
        #$row_data{'comment_id'} = $_->comment_id;
        $row_data{'post_subject'}       = $_->subject;
        $row_data{'post_author'}    = $_->author;
        $row_data{'post_date'}        = $_->date;
        $row_data{'post_id'}        = $_->postid;
        $row_data{'post_content'}    = $_->story;
        #$row_data{'is_hidden'}  = $_->is_hidden;
    
    
        # push it into the the loop....
        push ( @end_posts, \%row_data );
    }
    
    $tmpl->param( 
        site_title    => $self->param('main_page_title'),
        site_css      => 'css/main.css',
        post_data     => \@end_posts,
        next_page     => $comments->pager->next_page,
        previous_page => $comments->pager->previous_page,
    );
    
    $tmpl->output;

}

[download]

All I can think of is maybe  page => $q->param('next_page'), this attribute is somehow effing things up, but it's creating the necessary two pages...so I really can't say.

I've scoured all the docs and I just can't find anything. Am I missing something monks?

UPDATE:Looks like changing page => $q->param('next_page') to page => $q->param('next_page') || 1 and 

my $comments = $schema->resultset('News')->search(
        undef,
        { 
          rows => 10,
          page => $q->param('next_page'),
          order_by => 'date DESC' 
        }
    );

[download]
to 
my $comments = $schema->resultset('News')->search(
        {},
        { 
          rows => 10,
          page => $q->param('next_page'),
          order_by => 'date DESC' 
        }
    );

[download]
did the trick...(thanks LTJake :-))

meh.

Replies are listed 'Best First'.
Re: DBIx::Class Pagination troubles
by castaway (Parson) on Sep 25, 2006 at 16:10 UTC
    Try turning on the sql debugging: DBIC_TRACE=1 in your environment.. Also, why not just pass the entire pager to TT, rather than extracting some data then passing the rest?

    That code looks ok to me, but I haven't tried it directly, without using the pager at all.

    C.

[reply]

      I'm not sure what you mean by pass the entire pager to H::T, but I think you mean the pager object? I've tried this, and it spewed an error, something along the lines of "can not paginiate unpaged object" or something to that effect...

      meh.
[reply]
Re: DBIx::Class Pagination troubles
by haoess (Curate) on Sep 26, 2006 at 07:12 UTC

    Don't use constructs as hash values, that could return empty lists:

    $ cat test.pl
  #!/usr/bin/perl

  use warnings;
  use strict;

  use CGI;
  use Data::Dumper;

  my $q = CGI->new;

  my %h = (
      one => $q->param('one'),
      two => $q->param('two')
  );

  print Dumper \%h;
  __END__

$ perl test.pl
$VAR1 = {
          'one' => 'two'
        };

$ perl test.pl one=1
Odd number of elements in hash assignment at test.pl line 11.
$VAR1 = {
          'one' => '1',
          'two' => undef
        };

$ perl test.pl two=2
Odd number of elements in hash assignment at test.pl line 11.
$VAR1 = {
          'one' => 'two',
          '2' => undef
        };

$ perl test.pl one=1 two=2
$VAR1 = {
          'one' => '1',
          'two' => '2'
        };

    [download]

    To use the $q->param("...") contruct as a hash value, force scalar context:

      my %h = (
      one => scalar $q->param('one'),
      two => scalar $q->param('two')
  );

    [download]

    --Frank

[reply]
[d/l]
[select]
Re: DBIx::Class Pagination troubles
by mreece (Friar) on Sep 25, 2006 at 16:32 UTC
    i see you doing a push @end_posts, ... but don't see where @end_posts is declared/reset. is it possible it already contains some values before it gets to this for ... push loop?
[reply]
[d/l]
[select]
      @end_posts is declared earlier on in the config section of the script...it's only declared then, and not initialized until the main sub.
      meh.
[reply]
[d/l]
[select]
Re: DBIx::Class Pagination troubles
by fmerges (Chaplain) on Sep 30, 2006 at 00:05 UTC

    Hi,

    Every method on DBIx::Class where you want to pass additional options you do it after declaring an empty hashref... search({},{options...}).

    Another thing..., I suggest you to doing thinks a lil' bit cleaner, less problems...

    
 my $page = $q->param('page') || 1;
 my $rows = $q->param('rows') || 10;
 my $order_by = $q->param('order') || 'date DESC';

 # And you should check, untaint the data, before using,
 # even an option like page= ...

 # Your code for getting the comments could be written as
 my $rs = $schema-> ....

 # and then
 while (my $comment = $rs->next) {
    ...
 }
 # this way, less memory is getting used by perl

 # Why not say
 my $pager = $rs->pager;
 # instead of this $rs->pager->...

    [download]

    Regards,

    fmerges at irc.freenode.net
[reply]
[d/l]
Re: DBIx::Class Pagination troubles
by spatterson (Pilgrim) on Sep 27, 2006 at 10:47 UTC
    I used to do this manually, using the LIMIT/OFFSET SQL commands, where each page passed through the full SQL query, the page size & offset to the cgi script which would put them together, run the resulting query then put the original query, page size & new offset onto a 'next page' link.

    Its fairly simple but the logic could get interesting at times. I had 1 script which would either display a results page or an 'enter query' page depending on how it was called.

    just another cpan module author
[reply]

      that sort of defeats the purpose of DBIx::Class, especially since there's a working method provided. I could understand maybe if there was no method provided, but the point is to abstract the SQL away from the logic :-)

      meh.
[reply]
      Can you write a part of code making this manual paging. I'm looking to this sample to do my realization: 
      $C->{stuff} = $schema->storage->dbh_do(
sub {
my ($storage, $dbh, @cols) = @_;
my $cols = join(q{, }, @cols);
$dbh->selectrow_array("SELECT $cols FROM foo");
},
@column_list
);

      [download]
      But how to use it in TT? Is it return the same result as traditional $schema->search();
[reply]
[d/l]

Back to Seekers of Perl Wisdom