Jquery - How can I implement row-count in DataTables when the results are paginated?

RazvanZamfir
December 20, 2024
39 views
0 votes
2 Answers

I use DataTables to paginate and order a table containing user data.

The full data set comes from a MySQL database via a .NET application.

The first column of the table has to contain the (current) row count in a manner that would make the count "immune" to rearranging the rows (sorting, in other words). This is why I have used a CSS-based row count, as visible below:

new DataTable('#employees', {
  info: false,
  paging: true,
  "aLengthMenu": [5, 10, 20],
  // "dom": 'rtip',
});

.dt-column-order:before {
  margin-bottom: 1px;
}

th {
  outline: none !important;
}

.dt-paging {
  margin-top: 15px !important;
}

.pagination {
  justify-content: flex-end;
}

/* CSS row count */
table {
  counter-reset: row-num;
}

table tbody tr {
  counter-increment: row-num;
}

table tbody tr td:first-child::before {
  content: counter(row-num) "";
}

<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/jquery.slim.min.js"></script>
<script src="https://cdn.datatables.net/2.1.8/js/dataTables.min.js"></script>
<script src="https://cdn.datatables.net/2.1.8/js/dataTables.bootstrap5.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js"></script>
<link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://cdn.datatables.net/2.1.8/css/dataTables.bootstrap5.min.css" rel="stylesheet" />

<div class="container-fluid my-2">
  <h2>Data Tables</h2>
  <table id="employees" class="table table-bordered table-striped mx-1">
    <thead>
      <tr>
        <th data-dt-order="disable">No</th>
        <th>Name</th>
        <th>Position</th>
        <th>Office</th>
        <th>Age</th>
        <th>Start date</th>
        <th>Salary</th>
      </tr>
    </thead>
    <tbody>
      <tr>
        <td></td>
        <td>Tiger Nixon</td>
        <td>System Architect</td>
        <td>Edinburgh</td>
        <td>61</td>
        <td>2011-04-25</td>
        <td>$320,800</td>
      </tr>
      <tr>
        <td></td>
        <td>Garrett Winters</td>
        <td>Accountant</td>
        <td>Tokyo</td>
        <td>63</td>
        <td>2011-07-25</td>
        <td>$170,750</td>
      </tr>
      <tr>
        <td></td>
        <td>Ashton Cox</td>
        <td>Junior Technical Author</td>
        <td>San Francisco</td>
        <td>66</td>
        <td>2009-01-12</td>
        <td>$86,000</td>
      </tr>
      <tr>
        <td></td>
        <td>Cedric Kelly</td>
        <td>Senior Javascript Developer</td>
        <td>Edinburgh</td>
        <td>22</td>
        <td>2012-03-29</td>
        <td>$433,060</td>
      </tr>
      <tr>
        <td></td>
        <td>Ștefan Popa</td>
        <td>Accountant</td>
        <td>Tokyo</td>
        <td>33</td>
        <td>2008-11-28</td>
        <td>$162,700</td>
      </tr>
      <tr>
        <td></td>
        <td>Brielle Williamson</td>
        <td>Integration Specialist</td>
        <td>New York</td>
        <td>61</td>
        <td>2012-12-02</td>
        <td>$372,000</td>
      </tr>
      <tr>
        <td></td>
        <td>Herrod Chandler</td>
        <td>Sales Assistant</td>
        <td>San Francisco</td>
        <td>59</td>
        <td>2012-08-06</td>
        <td>$137,500</td>
      </tr>
      <tr>
        <td></td>
        <td>Rhona Davidson</td>
        <td>Integration Specialist</td>
        <td>Tokyo</td>
        <td>55</td>
        <td>2010-10-14</td>
        <td>$327,900</td>
      </tr>
      <tr>
        <td></td>
        <td>Colleen Hurst</td>
        <td>Javascript Developer</td>
        <td>San Francisco</td>
        <td>39</td>
        <td>2009-09-15</td>
        <td>$205,500</td>
      </tr>
      <tr>
        <td></td>
        <td>Sonya Frost</td>
        <td>Software Engineer</td>
        <td>Edinburgh</td>
        <td>23</td>
        <td>2008-12-13</td>
        <td>$103,600</td>
      </tr>
      <tr>
        <td></td>
        <td>Jena Gaines</td>
        <td>Office Manager</td>
        <td>London</td>
        <td>30</td>
        <td>2008-12-19</td>
        <td>$90,560</td>
      </tr>
      <tr>
        <td></td>
        <td>Quinn Flynn</td>
        <td>Support Lead</td>
        <td>Edinburgh</td>
        <td>22</td>
        <td>2013-03-03</td>
        <td>$342,000</td>
      </tr>
    </tbody>
  </table>
</div>

There is a problem with this solution though: whenever the results set is big enough so that there is pagination, the row count starts at 1 for every page.

So, I actually need a row count based on JavaScript, which would not reset the count for every page. I did not find a way to do this bult into DataTables.

Questions

Is there a built-in DataTables solution to this?
If not, what would be a viable solution to the problem?

Answers

In the example an event listener was added to document because the <table> and maybe elements higher up in the hierarchy were being destroyed and created. Anyways it was tricky trying to get the correct page but I got an index number by finding the <button> currently residing within <li class="active">. Now that the right index was correct I used an array with the 3 numbers that were the starting row numbers for each page. Finally, I got a CSS variable --start to change the value of counter-set which is also the second value of counter-reset assigned to <table>. So every time the page is changed that value changes from 0 to 5 and to 10.

new DataTable('#employees', {
  info: false,
  paging: true,
  "aLengthMenu": [5, 10, 20],
  // "dom": 'rtip',
});

document.addEventListener("click", adjCounters);

function adjCounters(e) {
  const clk = e.target;
  const sel = document.querySelector("#dt-length-0").value;

  if (clk.matches("button")) {
    const act = document.querySelector(".active .page-link")
    const len = Number(sel);
    const idx = Number(act.getAttribute("data-dt-idx"));
    const pgs = len * idx;

    const table = document.querySelector("table");
    table.style.setProperty("--start", pgs);
  }
}

:root {
  --start: 0
}

.dt-layout-full {
  padding: 0 !important;
}

.dt-column-order:before {
  margin-bottom: 1px;
}

th {
  outline: none !important;
}

.dt-paging {
  margin-top: 15px !important;
}

.pagination {
  justify-content: flex-end;
}

/* CSS row count */
table {
  counter-reset: row-num var(--start)
}

table tbody tr {
  counter-increment: row-num;
}

table tbody tr td:first-child::before {
  content: counter(row-num) "";
}

<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/jquery.slim.min.js"></script>
<script src="https://cdn.datatables.net/2.1.8/js/dataTables.min.js"></script>
<script src="https://cdn.datatables.net/2.1.8/js/dataTables.bootstrap5.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js"></script>
<link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://cdn.datatables.net/2.1.8/css/dataTables.bootstrap5.min.css" rel="stylesheet" />

<div class="container-fluid my-2">
  <h2>Data Tables</h2>
  <table id="employees" class="table table-bordered table-striped mx-1">
    <thead>
      <tr>
        <th data-dt-order="disable">No</th>
        <th>Name</th>
        <th>Position</th>
        <th>Office</th>
        <th>Age</th>
        <th>Start date</th>
        <th>Salary</th>
      </tr>
    </thead>
    <tbody>
      <tr>
        <td></td>
        <td>Tiger Nixon</td>
        <td>System Architect</td>
        <td>Edinburgh</td>
        <td>61</td>
        <td>2011-04-25</td>
        <td>$320,800</td>
      </tr>
      <tr>
        <td></td>
        <td>Garrett Winters</td>
        <td>Accountant</td>
        <td>Tokyo</td>
        <td>63</td>
        <td>2011-07-25</td>
        <td>$170,750</td>
      </tr>
      <tr>
        <td></td>
        <td>Ashton Cox</td>
        <td>Junior Technical Author</td>
        <td>San Francisco</td>
        <td>66</td>
        <td>2009-01-12</td>
        <td>$86,000</td>
      </tr>
      <tr>
        <td></td>
        <td>Cedric Kelly</td>
        <td>Senior Javascript Developer</td>
        <td>Edinburgh</td>
        <td>22</td>
        <td>2012-03-29</td>
        <td>$433,060</td>
      </tr>
      <tr>
        <td></td>
        <td>Ștefan Popa</td>
        <td>Accountant</td>
        <td>Tokyo</td>
        <td>33</td>
        <td>2008-11-28</td>
        <td>$162,700</td>
      </tr>
      <tr>
        <td></td>
        <td>Brielle Williamson</td>
        <td>Integration Specialist</td>
        <td>New York</td>
        <td>61</td>
        <td>2012-12-02</td>
        <td>$372,000</td>
      </tr>
      <tr>
        <td></td>
        <td>Herrod Chandler</td>
        <td>Sales Assistant</td>
        <td>San Francisco</td>
        <td>59</td>
        <td>2012-08-06</td>
        <td>$137,500</td>
      </tr>
      <tr>
        <td></td>
        <td>Rhona Davidson</td>
        <td>Integration Specialist</td>
        <td>Tokyo</td>
        <td>55</td>
        <td>2010-10-14</td>
        <td>$327,900</td>
      </tr>
      <tr>
        <td></td>
        <td>Colleen Hurst</td>
        <td>Javascript Developer</td>
        <td>San Francisco</td>
        <td>39</td>
        <td>2009-09-15</td>
        <td>$205,500</td>
      </tr>
      <tr>
        <td></td>
        <td>Sonya Frost</td>
        <td>Software Engineer</td>
        <td>Edinburgh</td>
        <td>23</td>
        <td>2008-12-13</td>
        <td>$103,600</td>
      </tr>
      <tr>
        <td></td>
        <td>Jena Gaines</td>
        <td>Office Manager</td>
        <td>London</td>
        <td>30</td>
        <td>2008-12-19</td>
        <td>$90,560</td>
      </tr>
      <tr>
        <td></td>
        <td>Quinn Flynn</td>
        <td>Support Lead</td>
        <td>Edinburgh</td>
        <td>22</td>
        <td>2013-03-03</td>
        <td>$342,000</td>
      </tr>
    </tbody>
  </table>
</div>

"Is there a built-in DataTables solution to this?"

Yes there is, assuming I have understood what you mean by "row count" in your question.

I assume you don’t mean "a count of the total number of rows"… But rather the row number, starting at "1" for your "Tiger Nixon" row, and "2" for the next row, and so on… And that number needs to be independent of sorting, filtering, and pagination: the "Tiger Nixon" row should always be row number "1" in the first column of the table, even when it is not displayed as the very first row seen by a user, due to sorting/filtering/pagination.

Every row is allocated an index number, based on the order in which the data is first loaded into the DataTable when the DataTable is initialized. That assigned index number never changes, unless you delete data from the DataTable or re-initialize the DataTable.

This index number is accessed via the DataTables API using row().index().

More usefully, in your case, the row index can be accessed by a column data function: function data( row, type, set, meta ), which is documented on this page.

See the definition for the meta parameter in that function. It gives you access to:

"the row index for the requested cell"

So, your demo can use meta as follows:

<!doctype html>
<html>

<head>
  <meta charset="UTF-8">
  <title>Demo</title>
  <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/jquery.slim.min.js"></script>
  <script src="https://cdn.datatables.net/2.1.8/js/dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/2.1.8/js/dataTables.bootstrap5.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js"></script>
  <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet">
  <link href="https://cdn.datatables.net/2.1.8/css/dataTables.bootstrap5.min.css" rel="stylesheet" />
</head>

<body>

  <div class="container-fluid my-2">
    <h2>Data Tables</h2>
    <table id="employees" class="table table-bordered table-striped mx-1">
      <thead>
        <tr>
          <th data-dt-order="disable">No</th>
          <th>Name</th>
          <th>Position</th>
          <th>Office</th>
          <th>Age</th>
          <th>Start date</th>
          <th>Salary</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td></td>
          <td>Tiger Nixon</td>
          <td>System Architect</td>
          <td>Edinburgh</td>
          <td>61</td>
          <td>2011-04-25</td>
          <td>$320,800</td>
        </tr>
        <tr>
          <td></td>
          <td>Garrett Winters</td>
          <td>Accountant</td>
          <td>Tokyo</td>
          <td>63</td>
          <td>2011-07-25</td>
          <td>$170,750</td>
        </tr>
        <tr>
          <td></td>
          <td>Ashton Cox</td>
          <td>Junior Technical Author</td>
          <td>San Francisco</td>
          <td>66</td>
          <td>2009-01-12</td>
          <td>$86,000</td>
        </tr>
        <tr>
          <td></td>
          <td>Cedric Kelly</td>
          <td>Senior Javascript Developer</td>
          <td>Edinburgh</td>
          <td>22</td>
          <td>2012-03-29</td>
          <td>$433,060</td>
        </tr>
        <tr>
          <td></td>
          <td>Ștefan Popa</td>
          <td>Accountant</td>
          <td>Tokyo</td>
          <td>33</td>
          <td>2008-11-28</td>
          <td>$162,700</td>
        </tr>
        <tr>
          <td></td>
          <td>Brielle Williamson</td>
          <td>Integration Specialist</td>
          <td>New York</td>
          <td>61</td>
          <td>2012-12-02</td>
          <td>$372,000</td>
        </tr>
        <tr>
          <td></td>
          <td>Herrod Chandler</td>
          <td>Sales Assistant</td>
          <td>San Francisco</td>
          <td>59</td>
          <td>2012-08-06</td>
          <td>$137,500</td>
        </tr>
        <tr>
          <td></td>
          <td>Rhona Davidson</td>
          <td>Integration Specialist</td>
          <td>Tokyo</td>
          <td>55</td>
          <td>2010-10-14</td>
          <td>$327,900</td>
        </tr>
        <tr>
          <td></td>
          <td>Colleen Hurst</td>
          <td>Javascript Developer</td>
          <td>San Francisco</td>
          <td>39</td>
          <td>2009-09-15</td>
          <td>$205,500</td>
        </tr>
        <tr>
          <td></td>
          <td>Sonya Frost</td>
          <td>Software Engineer</td>
          <td>Edinburgh</td>
          <td>23</td>
          <td>2008-12-13</td>
          <td>$103,600</td>
        </tr>
        <tr>
          <td></td>
          <td>Jena Gaines</td>
          <td>Office Manager</td>
          <td>London</td>
          <td>30</td>
          <td>2008-12-19</td>
          <td>$90,560</td>
        </tr>
        <tr>
          <td></td>
          <td>Quinn Flynn</td>
          <td>Support Lead</td>
          <td>Edinburgh</td>
          <td>22</td>
          <td>2013-03-03</td>
          <td>$342,000</td>
        </tr>
      </tbody>
    </table>
  </div>

  <script>
    $(document).ready(function() {

      new DataTable('#employees', {
        info: false,
        paging: true,
        "aLengthMenu": [5, 10, 20],
        // "dom": 'rtip',
        columnDefs: [{
          targets: [0],
          data: function(row, type, val, meta) {
            if (meta) {
              return meta.row + 1;
            } else {
              return null;
            }
          }
        }]
      });

    });
  </script>

</body>

</html>

This has the advantage that you are operating directly on the DataTables data – and not on the data which happens to be displayed in the DOM for a given page of visible data.

Why is that if (meta) needed? Because of DataTables’ support for orthogonal data, which allows you to store different values for a cell, for different purposes (what you see in a cell may be different from how you want to sort or filter that data).

There is one extra orthogonal type (set – used by plugins) which does not contain any meta data – so your code would throw an error (meta does not exist) without this check.

Alternatively…

For static row numbers (similar to Excel), then you need to re-write the display data every time the user performs a sort, filter or pagination action.

That does involve some JavaScript, using the DataTables API.

First, define a table variable, and also add an initComplete function, to handle the very first time the table is displayed:

var table = new DataTable('#employees', {
  info: false,
  paging: true,
  "aLengthMenu": [5, 10, 20],
  // "dom": 'rtip',
  initComplete: function() {
    writeRowNumbers();
  }
});

Add an on draw event, to handle all subsequent changes made by the user (sort/filter/paginate):

table.on( 'draw', function () {
  writeRowNumbers()
} );

The row numbers writer is straightforward – it just iterates over the visible (DOM) data in the displayed table:

function writeRowNumbers() {
  $('#employees tbody tr').each(function( index ) {
    $('td', this ).first().html(index + 1);
  } );
}

But again, maybe I have failed to understand what you mean by "row count"…

Please signup or login to give your own answer.

Click here to cancel reply.

Jquery – How can I implement row-count in DataTables when the results are paginated?

Questions

Answers