Friday, 16 April 2021

DataTable not getting refreshed after AJAX POST response is received onclick of next page

My problem statement is as below:

Display 10 rows per page on DataTable. When the user clicks on the next page number, use that number in flask to calculate row_count. Select rows from database that are equal to the above row_count. Display these selected rows on DataTable.

Following code gives me json response on console.log, but my DataTable is not getting refreshed.

JQuery snippet:

$(document).ready(function() {
       var buttonCommon = {
        exportOptions: {
            format: {
                body: function ( data, row, column, node ) {
                                return data;
                                }
                            }
                    }
            };

var table = $("#myTable").DataTable({
 "lengthMenu": [
   [10, 25, 50, -1],
   [10, 25, 50, "All"]
 ],
 drawCallback: function() {
   $(".paginate_button", this.api().table().container())
     .on("click", function() {

       var pageNum= table.page.info().page;

        $.ajax({
            data: JSON.stringify({ "pageNum": pageNum }),
            dataType: "json",
            type: "POST",
            contentType: "application/json; charset=utf-8",
            url: "/my/url",
            success: function(data){
                data = JSON.parse(data);
                $("#myTable").DataTable({
                    data: data.data,
                    columns: [
                            { "data": "name" },
                            { "data": "branch" },
                            { "data": "city" },
                            { "data": "state" },
                            { "data": "country" },
                            { "data": "pin" },
                            { "data": "landmark" },
                            ]
                    })
                }
            })
        })
    }   
})

Flask snippet:

@my_api.route('/url', methods=['POST', 'GET'])
def myfunc():

    conn = get_pgsql_conn_obj()
    cursor = conn.cursor()

    if request.get_json():
        number = request.json['pageNum']
        start_num = number * 20 + 1
        stop_num = start_num + 9

        select_sql = "select name, branch, city, state, country, pin, landmark from (select name, branch, city, state, country, pin, landmark, row_number() over (order by pin) as rnum from table) as r where r.rnum <= {}".format(stop_num)
     
        cursor.execute(select_sql)

        myData = cursor.fetchall()
    
        c_data = []
        for c in myData:
            c_data.append({
                'name': c[0],
                'branch': c[1],
                'city': c[2],
                'state': c[3],
                'country': c[4],
                'pin': c[5],
                'landmark': c[6],
                })
     
        return json.dumps(err_data)

    elif request.get_json() is None:

        number = 0
        select_sql = "select name, branch, city, state, country, pin, landmark from (select name, branch, city, state, country, pin, landmark, row_number() over (order by pin) as rnum from table) as r where r.rnum = 1 or r.rnum <= 20"
        cursor.execute(select_sql)

        myData = cursor.fetchall()


        c_data = []
        for c in myData:
            c_data.append({
                'name': c[0],
                'branch': c[1],
                'city': c[2],
                'state': c[3],
                'country': c[4],
                'pin': c[5],
                'landmark': c[6],
                })

        return render_template('my.html', c_data=c_data)

    else:
    *Do something else*

I can see the response in the Network Response something like below:

[{"name": "XYZ", "branch": "main", "city": "Tokyo", "state": "P3", "country": "Japan", "pin": "110011", "landmark": "L1"},{"name": "XYZ", "branch": "main", "city": "Tokyo", "state": "P3", "country": "Japan", "pin": "110011", "landmark": "L2"},...{"name": "XYZ", "branch": "main", "city": "Tokyo", "state": "P3", "country": "Japan", "pin": "110011", "landmark": "L30"}]



from DataTable not getting refreshed after AJAX POST response is received onclick of next page

No comments:

Post a Comment