What is the limit of records we can deal with?

allan · December 2014

Can you show me what you tried? You simply need to call ajax.reload() like in the examples.

Allan

redani · December 2014

var table;
            
table = $(".datatable").dataTable({
    "deferRender": true,
    "ajax": "/path/file.php",
    "lengthMenu": [[50, 200, 500, 1000, -1], [50, 200, 500, 1000, "All"]],
});
            
            
$("#filters .filter:not(.active)").click(function() {
    var removed = $(this).data("filter");
            
    //table.ajax.reload();
    //table.ajax.url("/path/file.php?removed=" + removed).load();           
});

I tried the 2 commented lines, but none of them worked :/ i'm sure i'm missing something!!

allan · December 2014

Change:

table = $(".datatable").dataTable({

to:

table = $(".datatable").DataTable({

I would be very surprised if you weren't getting an error before - one which is covered by the second top FAQ.

Allan

redani · December 2014

Wow! i noticed the difference but never thought it would fix the problem :D thanks :)
is there a way to use a callback function with ajax.url() ?

in my previous messages i asked you about using dataTable with combo boxes (<select>) and you said that it can be done using columns.renderDT. I found a work around if it can help anyone, and i'd also like you please to tell me if it affect any speed performance:
the solution is:


<select>
    <option>Value1</option>
    <option selected="selected">Value2</option>
</select>
<span class="hidden-txt">Value2</span>

// css
.hidden-txt {
    position: absolute;
    clip: rect(0px 0px 0px 0px);
}

the text will not be displayed and will be taken into consideration by the search engine :)

What do you think??

allan · December 2014

is there a way to use a callback function with ajax.url() ?

To do what? ajax.reload() has a callback on complete.

What do you think??

Sounds fair to me.

Allan

redani · December 2014

Yes I know ajax.reload() has a callback function, but i'm using ajax.url().load(), and didn't find its callback function in the doc...
In datatable initialization i get an extra data with initComplete, I'd like to update this extra data when i reload the new datatable content:

"initComplete": function(settings, data) {
    $("#extra").html(data.extra);
}

allan · December 2014

The ajax.url().load() method's documentation states that the first parameter is a callback.

Allan

redani · December 2014

Awtch!! sorry, i was looking at the ajax.url() doc :/
Everything works like a charm, thanks for all ;)

redani · December 2014

I managed to fix it :)

Thanks and happy holidays ;)

redani · December 2014

Please how can I use footerCallback with deferRender? or in other words, execute footerCallback only when the table is populated...

Thank you

allan · December 2014

The deferRender option should have no effect on the footerCallback. Obviously not all of the DOM elements might be available (that is the whole point of deferRender after all), but you should still be able to work with the data for the table.

Allan

redani · December 2014

Hi,

with deferRender, i get $NaN ( $NaN total), but i'd like it to display the real sum amount!
here is the code i'm using:

var table = $(".datatable").DataTable({
                "deferRender": true,
                "ajax": "/path/file.php",
                "lengthMenu": [[10, 200, 500, 1000, -1], [10, 200, 500, 1000, "All"]],
                "initComplete": function(settings, data) {
                    $("#extra").html(data.extra);
                },
                "columns": [
                    { className: "number" },
                    { className: "total-order" },
                    { className: "dt" },
                    { className: "status" },
                    { className: "payment" }
                ],
                
                "footerCallback": function ( row, data, start, end, display ) {
                    var api = this.api(), data;
                    
                    // Remove the formatting to get integer data for summation
                    var intVal = function ( i ) {
                        return typeof i === 'string' ?
                            i.replace(/[\$,]/g, '')*1 :
                            typeof i === 'number' ?
                                i : 0;
                    };
            
                    if ( api.column( 1 ).data().length ) {
                    
                        // Total over all pages
                        total = api.column( 1 ).data().reduce( function (a, b) {
                            return intVal(a) + intVal(b);
                        } );
                        
                        // Total over this page
                        pageTotal = api.column( 1, { page: 'current'} ).data().reduce( function (a, b) {
                            return intVal(a) + intVal(b);
                        }, 0 );
                    }
                    else {
                        total = pageTotal = 0;
                    }
                    
                    // Update footer
                    $( api.column( 4 ).footer() ).html(
                        '$'+pageTotal +' ( $'+ total +' total)'
                    );
                }
            });

I really don't see where the problem is!
my table has 5 columns, just like in your demo!

redani · December 2014

Hi, I found the problem :)
The column containing the price was also containing some html code (<span></span>) and that's why it was displaying "NaN"
here is the solution I found, maybe it can be useful for someone:
replace i.replace(/[\$,]/g, '')*1 by i.replace(',', '.').replace(/[^0-9.]/g, '')*1
since the price is formated like 10,40 €, i first replace "," by "." then remove all non numeric characters except for the "."

and in the end, it's better to use total.toFixed(2) to keep only 2 number in decimals...

I would have one other and maye last question please :)
how can I sum prices of only rows with a given class name?
I found this link http://datatables.net/reference/api/columns().footer() but in my case .sum can be in a <tr>, not <td> :/

Thanks ;)

allan · December 2014

Use cells().data() with the cells() selector being given a row selector and cell selector (the documentation for cells() explains that.

Allan

redani · December 2014

Great, thanks ;)
I'm also facing other issues :/

1- according to my researches, i can' do much about it but i'd love to have your opinion:
I would love to keep the order of records that came with my SQL query, I actually select all orders ordered by date DESC, but when datatable loads, it changes the sorting, so i used "order": [[ 2, "desc" ]], the number 2 being the number of "date" column. however since i'm displaying dates in french, the sorting is all messed up, it displays for example 9 decembre 2015 before 12 decembre 2015. the solution I came up with is the display a hidden <span>2015-12-09</span> so it can sort correctly. do you have a better solution?

2- DT_RowData is not working, my Json response is correct, but the <tr> doesn't contain the data attribute! even in this example: http://live.datatables.net/medowoco/1/edit it doesn't show data attribute in my browser inspector!! do you have an idea what I'm missing??

Thanks

allan · December 2014

however since i'm displaying dates in french, the sorting is all messed up, it displays for example 9 decembre 2015 before 12 decembre 2015.

Have a look at my blog post from yesterday. If you try it, let me know how you get on with it!

2- DT_RowData is not working, my Json response is correct, but the <tr> doesn't contain the data attribute!

If you want to use attributes, use the DT_RowAttr option. The DT_RowData option uses $().data() which doesn't write to the DOM if it doesn't need to.

Allan

redani · December 2014

Thanks a lot for your prompt reply :)

1- I'll try it right away and will let you know

2- I tried both DT_RowAttr: {attr: "2,95"} and DT_RowAttr: {data-attr: "2,95"} and my <tr> still doesn't contain any data attribute!

redani · December 2014

Alright, have just realized that i'm running the version 1.10.4, i'll proceed for the update now... :)

DT_RowAttr: {data-attr: "2,95"} WORKS FINE WITH 1.10.5-dev :)
Thanks ;)

however the "moment" can't make it work, it shows an error when i initiate with
$.fn.dataTable.moment( 'D MMMM YYYY' ); maybe cause i'm displaying the french months?

redani · December 2014

I'm almost done :)
please, in stead of using total = api.column(1).data().reduce... how can i sum the value contained in the "data-val='12.0'" in each <tr> ??

Thank you

redani · December 2014

Hi, i'm sorry to bother you again. I'm really stuck and need to finish the page i'm working on.
I'll have 2 questions please:
1- I spent to whole day searching how i can read data-atribute value located in a <tr>, how can I do so?

2- i'll need to use a date filter, let's say I want to display all rows from dec 1st to dec 15th, i set the filter in the main page, but i don't know how i can transmit the dates to the ajax page

Thanks a lot!

EDIT: I managed to send extra parameters but appending them to the ajax url:
"deferRender": true, "ajax": "/path/file.php?selected_date=<?php echo $selected_date; ?>"

Hower i'm still stuck with getting the "data-attribute=12" value :/

allan · December 2014

1- I spent to whole day searching how i can read data-atribute value located in a <tr>, how can I do so?

Can you not simply use $().data(...);? Can you post some code showing what you currently have?

Allan

redani · December 2014

Thank you Allan for your reply. I though about using $("selector").data("..."); but i don't know how i can sum only values of current page and of all pages. here is my code:

var table = $(".datatable").DataTable({
                "deferRender": true,
                "ajax":"/path/file.php?selected_date=<?php echo $selected_date; ?>",
                "lengthMenu": [[10, 200, 500, 1000, -1], [10, 200, 500, 1000, "All"]],
                "initComplete": function(settings, data) {
                    $("#extra").html(data.extra);
                },
                "asStripeClasses": [''], // remove odd & even class
                "order": [[ 2, "desc" ]],   
                "footerCallback": function ( row, data, start, end, display ) {
                    var api = this.api(), data;
                    
                    // Remove the formatting to get integer data for summation
                    var intVal = function (i) {
                        return typeof i === 'string' ? i.replace(',', '.').replace(/[^0-9.]/g, '')*1 : typeof i === 'number' ? i : 0;
                    };
                    
                    
                    if(api.cells(".sum", 1).data().length) {
                        // Total over all pages
            // it's here where i need to sum the data-attr values
                        total = api.column(1).data().reduce( function (a, b) {
                            return intVal(a) + intVal(b);
                        } );
                        
                        // Total over this page
            // and here where i need to sum the data-attr values of current page
            
                        pageTotal = api.cells(".sum", 1, { page: 'current'} ).data().reduce(function (a, b) {
                            return intVal(a) + intVal(b);
                        }, 0);
                    }
                    else {
                        total = pageTotal = 0;
                    }
                    
                    // Update footer
                    $(api.column(4).footer()).html(pageTotal.toFixed(2) + ' € (' + total.toFixed(2) +' € total)');
                }
            });

Thank you

allan · December 2014

I see - is this the only thing you need the data attributes for? I would very strongly suggest not to use them if that is the case as it will just degrade the performance, when you can do what you need without touching the DOM at all.

If your JSON response contains (for example): "sum-val" for each row, you could simply do:

table.rows().pluck( 'sum-val' ).sum();

using the pluck() method and the sum() plug-in.

Allan

redani · December 2014

Sorry, I dot really understand what you mean with "If your JSON response contains (for example): "sum-val" for each row" how can I add "sum-val" for each row?

Also maybe it'll be a better idea to use serverside instead of deferrender, do you agree?

allan · December 2014

Can you show me your JSON data? You must have DT_RowData: { ... } or similar at the moment. I'm suggesting you use put the value you need at the top level (i.e. not in DT_RowData or anything else).

Also maybe it'll be a better idea to use serverside instead of deferrender, do you agree?

No. If you use server-side processing, you would need to sum all of the values at the server (hence - "server-side processing"). The client-side method would not work.

Allan

redani · December 2014

Here is the json response for the first row:

0: "data column 1"
1: "data column 2"
2: "data column 3"
3: "data column 4"
4: "data column 5"
DT_RowAttr: { data-toteur: 0 }
DT_RowClass: "os12"
DT_RowId: "100001"

No. If you use server-side processing, you would need to sum all of the values at the server

I know, and i'm thinking that it could be faster to return the sum using the SQL query, honesty after thinking about it, i don't need the sum of current page, i'll only need the total sum of results...

I'm currently dealing with 4000 records, and growing fast, so is it better to use serverside?

allan · December 2014

50k records is normally when I switch to server-side myself - as a rule of thumb. Other considerations can have an effect - if you were to support IE6, you might switch at 2.5K for example...

For your data - my suggestion is to move data-toteur to the top level, then you can use table.rows().data().pluck( 'data-toteur' ).sum() to sum the data.

Allan

redani · December 2014

I'm not considering IE6, so i keep using deferRender :)

I now have the following results:

0: "data column 1"
1: "data column 2"
2: "data column 3"
3: "data column 4"
4: "data column 5"
DT_RowClass: "os12"
DT_RowId: "100001"
"toteur": "20"

but when I use table.rows().data().pluck( 'toteur' ).sum() it returns an error in the console! I'd like to inform you that I'm only displaying 5 columns!

Actually where should the above code be placed??

allan · December 2014

it returns an error in the console!

What is the error? It really would be useful if you could give me a link to the page so I can debug it. I'm really only guessing at the moment.

Actually where should the above code be placed??

Wherever you want to perform the sum. Also, once you have assigned the table variable (or replace with whatever you do use).

Allan

redani · December 2014

Hi,

As you can notice, it' the alert(table.rows().data().pluck( 'toteur' ).sum()); that is causing the error!

What is the limit of records we can deal with? - Page 2

What is the limit of records we can deal with?

Answers

Howdy, Stranger!

Categories

DataTables

What is the limit of records we can deal with? - Page 2

What is the limit of records we can deal with?

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables