Jquery datatable: add rows from database in a loop

Question

I have a page that makes an Ajax call to a database on page load. It returns a list of companies that the user is following and then iterates the list trough and does another Ajax call to each company, to get some statistics about them. The statistics are then added to a Datatable which is being drawn quite a few times:

var table = $("#example").DataTable();

        $.ajax({
            type: "POST",
            contentType: "application/json; charset=utf-8",
            url: "../json/stats.aspx?t=followed-companies",
            dataType: "json",
            success: function (data) {
                for (i = 0; i < Object.keys(data).length; i++) {
                    $.ajax({
                        type: "POST",
                        async: true,
                        contentType: "application/json; charset=utf-8",
                        url: "../json/stats.aspx?t=company-stats" + "&n=" + data[i]
                    }).done(function (dataSecondary) {

       var stringtest = "<tr><td>" + dataSecondary.Stats1 + "</td>" +
                            "<td>" + dataSecondary.Stats2 + "</td>" +
                            "<td>" + dataSecondary.Stats3 + "</td>" +
                            "<td>" + '<button type="button" id="delete" class="btn btn-danger btn-xs dt-delete">' + "Delete" + "</td></tr>";
                        table.row.add($(stringtest)).draw();
                    });
                }
            },
            error: function (xhr) {
                console.log('error', xhr);
            }
        });

I do not have much experience in programming, but I do know that this takes ages to load and so has a very poor performance.

Main question is, can I use the table.draw(); after I have added the rows in that loop? I guess that could save some time. I tried to draw the table after the loop is finished but the table said that there is no data to show.

Second question, is this bad practise (mainly those nested Ajax calls with loops and SQL queries), and should I try to unify as many SQL queries as possible?

I think it's bad practice. Your loop basically says 'hit the database every single time'. That's going to take ages to load. Why not try getting all the things you want once in an array or something and then display them in any way you want? I'm pretty sure a for loop with ajax calls will kneel your website's speed. — Jabberwocky
– Jabberwocky, Commented Jul 24, 2018 at 13:34

Roy Scheffers · Accepted Answer · 2018-07-24 19:04:47Z

1

JSFiddle example - Get Post Data using ajax and display in .dataTable()

Main question is, can I use the table.draw(); after I have added the rows in that loop?

Yes you can. If you look at the docs of datatables you'll see that the draw() function is made for that.

When you perform an action such as adding or deleting a row, changing the sorting, filtering or paging characteristics of the table you'll want DataTables to update the display to reflect these changes. This function is provided for that purpose.

Second question, is this bad practise (mainly those nested Ajax calls with loops and SQL queries), and should I try to unify as many SQL queries as possible?

Yes it's bad practice to have many calls going to your backend. If you have control over the backend, you should aim to optimise it. For example: When you supply a list of companies, it will return the stats for all those companies specified.

Currently it will especially be a bad experience for those with a slow internet connection e.g. mobile users.

Also, this solution doesn't scale well. Imagine your website taking off and 100s of individuals are using it simultaneously. That means 100 * amount-of-followed-companies calls, while with the suggested it would be 100 calls to the backend for fetching the same data.

Have you also considered the possibility to generate a table using ajax sourced data.

UPDATE:
As for the draw() function that is not working. According to the docs, you provide the data structure without actually building the table with html yourself. Use the data returned instead where columnNameX is the name of the columns you specified. Give this a try in your .done().

var data = {
  columnName1: dataSecondary.Stats1
  columnName2: dataSecondary.Stats2 
  columnName3: dataSecondary.Stats3
}

table.row.add(data).draw();

As for generating the button. I haven't worked with them before but you can find more about them here.

edited Jul 24, 2018 at 19:04

answered Jul 24, 2018 at 13:59

Roy Scheffers

3,90611 gold badges33 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

willberthos Over a year ago

Thank you for your insight! I will be looking in to the optimization part now that I got this working. How ever, the .draw() does not work if it is not inside the loop.

Roy Scheffers Over a year ago

Do you receive an error message? Try the updated part of my answer.

Roy Scheffers Over a year ago

I've added a link to a working example. You'll find the link at the top of the answer. Check also the console log of the jsfiddle. Data returned from the ajax call is logged there too, so you know what comes back from the server.

willberthos Over a year ago

The reason I could not use draw outside the loop was because of the asynchronous Ajax call.

Collectives™ on Stack Overflow

Jquery datatable: add rows from database in a loop

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related