How to reference the first row to pass a key for server-side pagination

jc0966

Hi,
I've looked at the server-side questions, but have slight variation.
When a page (of 1000 rows) is displayed in data tables, using the server-side option, I have the "Next", "Previous" buttons displayed.
"Next" requires the value in Row-1000, Col 2
"Previous" requires the value in Row-1, Col 2

This is my html:

<table data-src="objects" data-endpoint="{{ url('/customers/get-data/customers-by-name') }}" class="dTable display" cellspacing="0" \
width="100%">
  <thead>
  <tr>
    <th data-column="Col-1">Col1</th>
    <th data-column="Col-2">Col2</th>
    <th data-column="Col-3">Col3</th>
  </tr>
  </thead>
</table>

And the javascript:

$('.dTable').DataTable({
    "ajax": {
        "url": url,
        "dataSrc": src,
        "data" : function (d)  {
            d.startLastName = <something-here>.rows(0).data[1];;
        }
    },
    "pagingType": "simple",
    "pageLength": 100,
    "serverSide": true,
    "columnDefs": columnDefs,   
    "columns": columns
});

url, src, columnDefs, columns are defined and set previously.
When I hardcode "d.startLastName = '' ", I get the first page displayed.

How do I reference the first/last rows in the <something-here> the first time and on subsequent "Next"/"Previous" button clicks.

Constraints:
1) The app cannot use client side paging (pageLength has been set to 100 for testing/debugging purposes).
2) There is no correlation between the row number and the First/Last Name, so I cannot use DataTables "start" parameter, which is a number.

I'd appreciate some help on this.

Thanks.

JC.

glenderson

serverSide is typically for many records long database tables.

If you are using serverside for a database, then you need to create the sql statement that will return the records based upon the start and length as part of you ajax page. Your returned dataset is always exactly what is displayed. If you format your data as a json response, dataTables handles the input and rendering.

If you have static source, then using serverSide doesn't make too much sense as the data is already available and dataTables can easily handle 1000's of records natively. Are you attempting to carve up a static file?

jc0966

serverSide is typically for many records long database tables.

• Gazillions~

If you are using serverside for a database, then you need to create the sql statement that will return the records based upon the start and length as part of you ajax page. Your returned dataset is always exactly what is displayed. If you format your data as a json response, dataTables handles the input and rendering.

• All done, except to pass criteria from the first row of the currently displayed datable page (or none if it is the first time)

If you have static source, then using serverSide doesn't make too much sense as the data is already available and dataTables can easily handle 1000's of records natively.

• As stated above, not a a static source

To summarize my problem again...
The paging in this is NOT based on a row number or rowId. It is based on a column field in the first row (for NEXT) or last row (for PREVIOUS) in the currently displayed datable page. I do not know how to reference this row/desired column in the "data" attribute I've shown in the code above..

glenderson

Maybe I'm not getting what you are after, but paging is based upon what every order you decide based upon how you sort your table. If you are using mySQL, what is your limit clause?

select .. from ... where ... order by ... limit [start], [length]

start and length are passed from datatables in the URL or POST parameters upon a click. Paging is never based upon row id or row number (unless your sort it that way). It's based upon the SQL statement and the SQL engine fetches the record starting at [start], for a [length] records.

jc0966

Let's look further into the details of the "where " clause and maybe you will understand the problem.

The where-clause should have some criteria to get the next set.
Something like this..

where "customerName > 'some-val'"

In my case, some-val is from the first / last row , and in the Customer Name column in the currently displayed page (when the "Next/Previous" button is clicked)

So I need to get these values in the currently displayed page and pass by the "data" function (or some other way)

To diagram it for you (with page size of 3 rows)

Current page

Customer# CustomerName Age NetWorth

======== ============ === =======

200 Alex 10 111.00

100 Bob 56 222.00

300 Charlie 23 333.00

Previous Next

On clicking "Next", the where clause would be "where CustomerName > 'Charlie'".
Similarly if "Previous" was clicked, the where clause would be "where CustomerName < 'Alex'"

How to extract "Charlie" or "Alex" from this page when "Next" or "Previous" is clicked respectively to pass for the construction of the where-clause?

glenderson

Your SQL is wrong. Your condition should not be if "name > Charlie", It should be "order by name". Which column to sort by is include in the URL to your ajax page.

Select * From table order by name, this returns all the data in the database.
Select * From table order by name limit 100 - return the first 100 records
Select * From table order by name limit 200, 100 - returns records starting at 200 based upon the where condition, for 100 records.

This is what dataTables creates, as part the ajax URL (I'm using "get")

&draw=2&columns[0][data]=0&columns[0][name]=&columns[0][searchable]=true&columns[0][orderable]=true&columns[0][search][value]=&columns[0][search][regex]=false&columns[1][data]=1&columns[1][name]=&columns[1][searchable]=true&columns[1][orderable]=true&columns[1][search][value]=&columns[1][search][regex]=false&columns[2][data]=2&columns[2][name]=&columns[2][searchable]=true&columns[2][orderable]=true&columns[2][search][value]=&columns[2][search][regex]=false&order[0][column]=0&order[0][dir]=asc&start=50&length=50&search[value]=&search[regex]=false

Notice the &start and the &length. That tells my database engine the starting and number of records to display.

If I hit Next, the start goes up by 50.

If I change the pagemenulength to 10, then dataTables will figure out my start and length. You don't have to.

The order[0][column]=0 says my order by clause is the first column
&order[0][dir]=asc, says that I'm ascending order.

Bottom line - Your query is wrong.

jc0966

Thank you for your attention to my problem, but your focus on the wrong thing.
I understand the SQL and length and start. But I cannot explain more details about it due to revealing some confidential info. So I have used a Customer example.

I understand and use LIMIT and ORDER BY etc and all that is in place.

Also, your select statements "limit 200 100" is fine if your data is static. But in my case, data could be inserted into the database in the time the user goes to click "Next" or "Previous". So Row 200 is no longer 200 but now maybe 250.

My problem is that I just need to access the data that is displayed in the current page when the Next or Previous pagination button is clicked.

And now I have solved that part with this:

"data" : function (d) {

    var idx = $('.dispTable tr:first th:contains("CustomerNm")').index(); //Column index of column with "CustomerNm"

     d.startItem = $('.dispTable tr:eq("1") td:eq("' + idx + '")').text();

}

This gives me the value on row-1 Customer Name column .
I can do similar for the last row.
So I am all set on this part.

Now I need to know whether the Next or Previous button is clicked, to generate a ">" or "<" in my where-clause (I am using pagingType Simple, so it has only these two buttons).
How can I determine which button was clicked?