Pagination issue with jquery DataTables and will_paginate gem on Rails 5.2

Question

I am implementing jQuery DataTables in my application based on this tutorial. I could use a packaged Gem, but I need to understand and learn what's underneath.

My purpose is to display the Values from a ValuesList in a way the user can play with the list (search, sort, paginate).

The result fits for purpose, but I still have one issue: when displaying a dataset of 26 records, by pages of 10 records, only the first page contains data. Clicking the Next button returns an empty page (but correctly counts the missing records: "Showing 11 to 20 of 26 entries").

Searching the log, I found out that when first diplaying the page, the request sent to the database is:

SELECT "values".* 
       FROM "values" 
       WHERE (values_list_id = 938) 
       ORDER BY code asc 
       LIMIT $1 OFFSET $2  [["LIMIT", 10], ["OFFSET", 0]]

After clicking the Next button, the next query is:

SELECT "values".* 
       FROM "values" 
       WHERE (values_list_id = 938) 
       ORDER BY code asc 
       LIMIT $1 OFFSET $2  [["LIMIT", 10], ["OFFSET", 100]]

The same test with 25 records par page generates the Following request:

SELECT "values".* 
       FROM "values" 
       WHERE (values_list_id = 938) 
       ORDER BY code asc 
       LIMIT $1 OFFSET $2  [["LIMIT", 25], ["OFFSET", 625]]

It looks like the pagination feature calculates an OFFSET by LIMIT^2 ! But as I use will_paginate gem in several places, I know it works fine too.

So where does this come from, how can I solve it?

---

Here is my code:

Gemfile extract

gem 'will_paginate', '~> 3.1'

application.js

//= require jquery
//= require jquery_ujs
//= require bootstrap-sprockets
//= require turbolinks
//= require d3
//= require nested_form_fields
//= require jQueryRotate
//= require js.cookie
//= require select2/select2
//= require DataTables/datatables
//= require_tree .

application.scss

@import "oblique-4.1.1/styles/css/frutiger.css";
@import "oblique-4.1.1/styles/css/oblique-material.css";
@import "oblique-4.1.1/styles/css/oblique-core.css";
@import "oblique-4.1.1/styles/css/oblique-inline.css";
@import "oblique-4.1.1/styles/css/oblique-components.css";
@import "oblique-4.1.1/styles/css/oblique-utilities.css";
@import "bootstrap-4.1/modal.css";    
@import "DataTables/datatables.css";    
@import "select2/select2.css";    
@import "sis-prototype";

values_lists_controller.rb extract

  def get_child_values
    @child_values = Value.where('values_list_id = ?', @values_list.id).datatable_filter(params['search']['value'], params['columns'])
    values_total = Value.where('values_list_id = ?', @values_list.id).count
    values_count = @child_values.count

    @child_values = @child_values.datatable_order(params['order']['0']['column'].to_i, params['order']['0']['dir'])
    @child_values = @child_values.paginate(page: (params['start'].to_i + 1), per_page: params['length'])

    render json: { data: @child_values,
                   draw: params['draw'].to_i,
                   recordsTotal: values_total,
                   recordsFiltered: values_count }
  end

show.html.erb for ValuesList class

      <div class="tab-pane active" id="child-values">

        <div class="mat-button-group">
          <%= link_to values_list_path(@values_list, format: "csv"), class: "mat-flat-button mat-button-base mat-primary" do %>
            <span class="fa fa-file-excel"></span>
            Export to CSV
          <% end %>
          <%= link_to new_values_list_values_import_path(@values_list), class: "mat-flat-button mat-button-base mat-primary" do %>
            <span class="fa fa-file-alt" %></span>
            <%= t('ImportValues') %>
          <% end %>
        </div>

        <table class="table table-align-top" id="childTable1">
        </table>

      </div>
    </div>
  </div>

</section>

<script>
  $(document).ready( function () {
    $('#childTable1').DataTable({
      ajax: '<%= url_for(@values_list) %>/get_child_values',
      serverSide: true,
      columns: [
      {title: 'Code', data: 'code'},
      {title: 'Name', data: 'name'},
      {title: 'Id', data: 'id'}
      ]
    });
  } );
</script>

Answer 1

I finally found out that the start parameter does not return a page number, but a row number. I updated my code with the following:

# Calculate next page
page_number = (params['start'].to_i / params['length'].to_i) + 1
@child_values = @child_values.datatable_order(params['order']['0']['column'].to_i, params['order']['0']['dir'])
@child_values = @child_values.paginate(page: page_number, per_page: params['length'].to_i)