paging example

Notifications.pm

package Rebus::Controller::Notification;
use Mojo::Base 'Mojolicious::Controller';                          
                                                                                    
sub list {                                                                                                         
  my $c      = shift->openapi->valid_input or return;
  my $input  = $c->validation->output;
  my $output = [];                                   
                                                                                                
  my $rows = $input->{limit} // 14;                                                                                 
                                        
  my $where;
  if (defined($input->{before})) {                             
    my $beforeQuery = $c->db->resultset('Notification')->find($input->{before})->get_column('added');
    $where->{"me.added"} = {'>' => $beforeQuery};      
  }                                                                                                                       
  if (defined($input->{after})) {                                       
    my $afterQuery = $c->db->resultset('Notification')->find($input->{after})->get_column('added');
    $where->{"me.added"} = {'<' => $afterQuery};                        
  }                                                      
                                                                                     
  my $notificationResults = $c->db->resultset('Notification')->search(
    $where,                                                                                    
    {                                                  
      order_by => {'-desc' => 'added'},
      rows => $rows + 1               
    }                                                  
  );                                                 
                                      
  my ($firstID, $lastID);                                          
  my $count = 0;                                                                                               
  for my $notificationResult ($notificationResults->all) {                                                     
    $count++;                                                                                                        
    $firstID = $notificationResult->id if ($count == 1);             
    $lastID  = $notificationResult->id if ($count == $rows);                                       
    push @{$output}, $notificationResult->as_hash if ($count <= $rows);           
  }                                                                                                                  
                                        
  # Add pagination headers                                                                                       
  my @links;                                                                                             
  push @links,                                           
      '<'                                                                            
    . $c->req->url->clone->query(['limit' => $rows, 'before' => $firstID, 'after' => undef])->to_abs->scheme('https')
    . '>; rel="prev"'                                                           
    if (defined($firstID));                                             
  push @links,                                                                               
      '<'                                                                                          
    . $c->req->url->clone->query(['limit' => $rows, 'before' => undef, 'after' => $lastID])->to_abs->scheme('https')
    . '>; rel="next"'                                                                                          
    if ($count > $rows);                  
                                         
  $c->res->headers->add('Link' => join(',', @links)) if @links;    
                                                                                                  
  return $c->render(openapi => $output, status => 200);
}

1;

Remember.. paging is only reliable if you know your sorting your results the same way every time.. if you're paging making sure you're adding an explicit order_by to the db query ;) In the case above I'm using reverse date added order.

The above example does not use dbic's native pager object.. you could use that easily enough to add details like 'total'.. but in my case the total was rather volatile so it wasn't helpfull ;)