How can I globally merge and control pagination and sorting for a nested data table?

nightmare637

I have a table with two rows: the first row being headers, the second containing the data. Each column in the second row of my table contains a table itself, which is a datatable. Observe the following fiddle:

https://jsfiddle.net/b3fayocg/

Note: Not sure why the css isn't loading properly, but the searching functionality and everything else still works.

The issue, as you can see from the fiddle, is that there isn't a way for me to browse to the next page. TC Name has 12 items, and NS Flow has 15. However, you can only view 10 at a time. The reason being is that the pagination that you see is associated with the parent datatable, not the child/nested datatable. Just as well, I can sort the nested data tables using the arrows, but clicking the arrows with the parent table yields no results.

How can I make it so that I can the parent table's pagination affect all the child tables, as well as the sorting? Ideally, I would also remove the arrow for the nested tables sorting as well and just have one.

kthorngren

I fixed the CSS issue. Your CSS is pointing to http://cdn.datatables.net/1.10.0/css/jquery.dataTables.css and you are using DT 1.10.19 js file. I changed the CSS to use the same version and now it loads. Not sure why 1.10.0 CSS isn't loading.

The #example table has one row. In that row you have three columns with each column containing a Datatable. The paging element for the #example table won't perform paging of the other tables. I added the paging element, via dom: "tp" to the child tables so you can see what I'm talking. Each child table has its own paging element.
https://jsfiddle.net/rgjopavh/

You won't be able to use the main table's paging elements to page the child tables. You will need to create your own paging element and use the page() API for the child tables. Your example shows hat each child table may have a different number of pages. How do you want this to work?

You might be able to use the order event of the main table to set the order of the table in the appropriate column. Watch out for the order event bubbling from the child to the parent Datatable causing an infinite loop. You may need to use jQuery one() instead of jQuery on() or something like event.stopPropagation.

Kevin

nightmare637

First, thank you so much for your help!!!

I think I'm really close to cracking this problem, but I cannot seem to find out how to manually sort a specific column in a table. Every where I search has answers on how to initialize your datatable, or how to reorder your columns, but nothing on how to explicitly call the order event on a specific column after your datatable has been initialized.

My idea for the ordering was to listen in on the order event of the main table, and grab the column and direction, like so:

        $('#example').on('order.dt', function(oSettings, sInput, iColumn, bRegex, bSmart) {
            let col = iColumn[0].col;
            let dir = iColumn[0].dir;
}

As I have already created a variable for my child tables prior to the event...

let mytable2 = $('td table').dataTable();

I can use the col and dir variables to get the corresponding datatable and direction I want to go. The specific column I want to order will always be column 0:

...continuing on from $('#example').on('order.dt')
...
...
mytable2[col].order([0,dir]).draw();

Unfortunately, this is not working and I get an error about how .order() is not a function. Any suggestions on what I can do to get this working? I believe if I can resolve this issue, I should be able to resolve my pagination issue as well. Thank you again for your reply!!!

colin

but I cannot seem to find out how to manually sort a specific column in a table

order() sets the order in the table, while sort() will sort the records in a result set, without affecting the table.

mytable2[col].order([0,dir]).draw();

This looks like it should be

mytable2.column(2).order(dir).draw()

or

mytable2.order([2,dir]).draw()

Colin

kthorngren

let mytable2 = $('td table').dataTable();

First you will need to use DataTable() instead of dataTable() to get an instance of the API. See the Accessing the API docs for more details.

mytable2[col].order([0,dir]).draw();

You will need to use the table() to get the API of the desired table. Something like this:

mytable2.table( col ).order([0,dir]).draw();

Kevin

nightmare637

For some reason, I'm still having issues.

Per kthorngren's suggestion, I looked into the API. I can use

let mytable = mytable2.api();

to get access to the API. I experimented with DataTable() as well (thanks for pointing out that dataTable() and DataTable() are completely different!). Maybe I'm just not understanding the difference between sort and order. I checked out this post: https://datatables.net/forums/discussion/25268/difference-between-order-and-sort, but I guess I'm still not clear.

If I have 3 rows of data in a given column, like so:

4
9
6

I want to press the little arrows at the top to order the data, alternating between ascending and descending, like so:

4
6
9

or

9
6
4

If I try, in my event code, the following:

$('#example').on('order.dt', function(o,s,i,b,b2){
   ...
   ...
   let mytable = mytable2.api();
   mytable.table(col).order([0,dir]).draw();
   ...
   ...
}

It does not work. My table does not get updated. In playing around with the code, I observed that:

console.log(mytable.table(col).data().sort());

Returns a list of arrays that contain all my values for the column I'm interested in, so I know I'm at least in the right place. I tried the following lines of code, with no success:

mytable.table(col).data().sort().draw();
mytable.table(col).data().order().draw();
mytable.table(col).data().order([0,dir]).draw();

What am I missing? And thanks for the responses!

kthorngren

Use let mytable2 = $('td table').DataTable(); to get the API instance. This will get the API instance of all the tables in your main/parent table. Here is a simple example that uses order() for the second table.
http://live.datatables.net/wirapapi/1/edit

The sort() API works with the Javascript data. More specifically it works with the result of an API call like column().data(). It does not affect the table order. The order() API changes the ordering in the table.

Kevin

kthorngren

I updated my example with the order event for the first table to update the second.
http://live.datatables.net/wirapapi/2/edit

You may also need to reset the order of the other child tables in your solution.

Kevin

nightmare637

Looks like the reason it was not working is because I had ordering set to false for my child table - this is why no matter what I did, I couldn't get the table to work. Your example really helped me see this! I set it to false because I wanted to remove the option to order from the child table, and only allow the user to order from the parent table.

That said, I'm getting stuck with the call stack size erroring out. I think I know why, just not sure how to break it:

let mytable = $('td table').DataTable();
$('#example').on('order.dt', function(e, settings, ordArr) {
    let col = ordArr[0].col;
    let dir = ordArr[0].dir;
    mytable.table(col).order([0,dir]).draw();

Since #example is the main table, and mytable is all the child tables, I run into issues with this event. Specifically, the $('#example').on('order.dt') event is called regardless of whether the initiator is the parent table or the child table. The problem then is that when I do

mytable.table(col).order([0,dir]).draw();

It fires off the $('#example').on('order.dt') event again. And so it ends up calling itself multiple times until we error out.

I suspect the reason this is happening is because mytable is a child of the #example table. If I look at your example, this does not happen because you have two separate tables.

Is there a clean way for me to prevent mytable from hitting the parent event for order.dt?

nightmare637

Okay, it's not pretty or clean, but this is what I did to get it working:

let hasRun = false;

$('#example').on('order.dt', function(e, settings, ordArr) {
    let col = ordArr[0].col;
    let dir = ordArr[0].dir;

    if (!hasRun){
        hasRun = true;
        mytable.table(col).order([0,dir]).draw();
        hasRun = false;
    }            
})

My datatable is now performing as expected. Thank you so much for all your help working with me through this!!!