How to fix a row with sorting enabled

Jeff A

Hi Im looking for a way to allow sorting but keep the first data row FIXED at top. See green row in image.

kthorngren

Is that row a total row or is there another reason why you want it at the top?

If its for totals maybe you can put it in the header row and use headerCallback to calculate. There is an example of this using the footerCallback here:
https://datatables.net/examples/advanced_init/footer_callback.html

Kevin

Jeff A

Thanks Kevin for the quick reply and example but the table is generated by software which I can't edit. This solution needs to be generic so it can work on many tables with different data in them. Oh and that snapshot I uploaded was only few columns. The table has several more.

kthorngren

I don't believe there are any config options or CSS options to help keep specific row(s) at the top. My understanding is all rows in the tbody are ordered resulting in custom coding to force a row to the top.

A custom sorting plugin may help but might be complex to write.

Another option may be to dynamically update or maybe inject the row after each draw to force it to the top. This would require making sure the row constantly has data that allows it to stay on top.

I'm thinking the easiest option may be to filter the row from the table and place the data into the header. This option will allow normal ordering operations to occur. If the row data needs updated then header can be updated.

Please provide more details of what criteria designates the row to stay at the top. Maybe someone can provide a more detailed / better option.

Kevin

Jeff A

Hmm perhaps the developers here can add a "do not sort rows" option where a user could specify which rows are not included in sort using the row index. such as donotsort[0]

Jeff A

Kevin do you have an example of that setup you mentioned above:
"I'm thinking the easiest option may be to filter the row from the table and place the data into the header. This option will allow normal ordering operations to occur. If the row data needs updated then header can be updated."

kthorngren

Sorry, I don't have or know of an example to copy a row into the header. Maybe a search of the forum will yield something. Would be interesting to try though. No promises but maybe I will give it a shot if there are no other options provided.

Kevin

kthorngren

Found a few minutes while watching basketball :-) The working example is here.

thead

        <thead>
          <tr>
            <th>Name</th>
            <th>Position</th>
            <th>Office</th>
            <th>Age</th>
            <th>Start date</th>
            <th>Salary</th>
          </tr>
          <tr class="second">
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
          </tr>
        </thead>

code

$(document).ready( function () {
  var table = $('#example').DataTable({
    orderCellsTop: true,   //move sorting to top header
  });
  
  // Get the index of matching row.  Assumes only one match
  var indexes = table.rows().eq( 0 ).filter( function (rowIdx) {    //check column 0 of each row for tradeMsg.message.coin
    return table.cell( rowIdx, 0 ).data() === 'Ashton Cox' ? true : false;
  } );
 
  // grab the data from the row
  var data = table.row(indexes).data();
 
  // populate the .second header with the data
  for (i = 0; i < data.length; i++) {
    $('.second').find('th').eq(i).html( data[i] );
  }
  
  // remove the row from the table
  table.row(indexes).remove().draw(false);

} );

This example assumes only one row matches the condition. In this case it searches for Ashton Cox and moves it to the header. This also assumes the row data is static and runs only once. Likely should put the code in initComplete.

If the data in the row can change then it will take a different approach.

Kevin

allan

I wrote a blog post a little while ago about a plug-in that can do absolutely sorted data in a table. If your data is in the table body, do that.

Otherwise, if it is always going to be at the header, insert a second row into the header as Kevin rightly says.

Allan

Jeff A

Thank you Kevin and Allan, I will review your posts and try it out.

Jeff A

Hi allan,
I'm trying your add-on but getting some errors. See attached.

Here's my page script:

// script for sorting tables j=jQuery.noConflict(); j(document).ready( function () { var nameType = $.fn.dataTable.absoluteOrder( 'All' ); j('#confirmit_agg_table_1').DataTable( { "paging": false, "searching": false, "order": [], "info": false, "scrollX": true, "scrollY": "600px", "scrollCollapse": true, fixedColumns: { leftColumns: 3 }, columnDefs: [ { targets: 0, type: nameType } ] } ); } );

Jeff A

Looks like its failing on the nameType declaration:

var nameType = $.fn.dataTable.absoluteOrder( 'All' );

Uncaught TypeError: Cannot read property 'dataTable' of undefined

Jeff A

I think I got past that issue by changing the $ to j for my case.
Now it does move the row to bottom on initial page refresh but it doesn't stay there upon user initiated sorting. Did I misunderstand what it is supposed to do (i.e. always keep it at bottom)?

// script for sorting tables j=jQuery.noConflict(); j(document).ready( function () { //var nameType = j.fn.dataTable.absoluteOrder( 'All' ); var nameType = j.fn.dataTable.absoluteOrder( { value: 'All', position: 'bottom' } ); j('#confirmit_agg_table_1').DataTable( { "paging": false, "searching": false, "info": false, "scrollX": true, "scrollY": "600px", "scrollCollapse": true, fixedColumns: { leftColumns: 3 }, columnDefs: [ { targets: 0, type: nameType } ] } ); } );

kthorngren

I forgot about this plugin and blog. Unfortunately I don't think its going to work for you as I believe you always want a particular row at the top regardless of which column is sorted.

The reason I don't think it will work for your situation is this comment in the blog:

If you alter the sorting that is applied to the table by clicking on the headers of those columns, you will be able to see that those items stay in position regardless of the sorting of the column. If sorting is applied to one of the other columns the rows will be sorted normally.

You can test this in the example table in the blog. I haven't looked at the pluging but it's possible that it can be modified to fit your needs.

Anyway, I think the way to solve your exception error is to change to this:
var nameType = j.fn.dataTable.absoluteOrder( 'All' );

Notice I replaced "$" with "j" to go along with your j=jQuery.noConflict() line.

Kevin

Jeff A

Yes you are right Kevin. I missed that lined in the blog. Would your solution accomplish what I'm trying to do?

kthorngren

Would your solution accomplish what I'm trying to do?

My code is simply an example of moving one row, based on a filter, to the header and removing it from the table. It only does this when the table is initially loaded.

If the data in that row doesn't change then this might be sufficient for what you want. A couple things should be changed though:
1. Move it to initComplete
2. The filter has the potential of getting more than one index depending on the filter match. This could be changed.

If the data in that row can change then my example is not going to work.

Kevin

Jeff A

Following up on this thread... Would really appreciate if the developers would consider adding this capability in the near future. I would envision it as an "Ignore when sorting" property with the row indexes to ignore identified.
Thanks in advance,
Jeff

allan

If you want a row to stay at the top or the bottom of the table (if I've understood correctly), why not just put it in the header or footer of the table?

Allan

Jeff A

Hi Allan,

I don't have control over that. The system creates the HTML page.

Jeff

kthorngren

Did you try the sample code I provided to move a particular row to the header?

Might not fit your needs but it looks like you only tried the code from the blog post.

Kevin

allan

You could use jQuery before the DataTable is initialised to simply move the header / footer rows into the header and footer.

Certainly, if you want a row to remain at the header or footer, having it in the header / footer is the way to do that, not modifying the sort of the table body.

Allan

Jeff A

Thanks Allan. but do please consider such a change for future editions.

allan

It would be possible via a custom ordering plug-in, but it isn't something that I'm currently planning to put into DataTables since, as I say, a header row should be in the header and a footer in the footer.

Having said that, the DataTables feature set (current and planned) is always evolving, so if there is a good use case for this I'm always open to suggestions!

Regards,
Allan

Jeff A

hi allan,
Moving the JS to the header created an unintended consequence when using the export and pdf buttons, since those only export 1 header row not multiple. Any ideas?

A colleague had me try this setup but it didn't have any effect when using the export buttons? Is there something similar you have seen that does work?

test

...put datatables JS here

<style>
@media print
{
.no-print, .no-print *
{
display: none !important;
}
}
</style>

Jeff

allan

Use orderCellsTop set to true to have it export only the top row (rather than the default bottom header row).

Allan