DataTables server-side processing - echoing back draw() parameter (Python/Flask)

Question

I'm using DataTables to display data from MySQL. It worked fine, until I was forced to use server-side processing (100k rows). Now, when I load the table in the browser, it works fine until I use some feature of DataTables (search, column sorting..). When I click on e.g. column name, all I got is "Processing..." message.

I noticed, that with every click on the table, the draw is raised by 1 in the XMLHttpRequest, but my 'draw' is still set to 1 in my code.

My definition of draw, recordsTotal, recordsFiltered in python/flask code(shortened):

tick = table.query.all()
rowsCount = table1.query.count()

x = {'draw':1,'recordsTotal':rowsCount,'recordsFiltered':10}
y = dict(data=[i.serialize for i in tick])
z = y.copy()
z.update(x)

@app.route("/api/result")
def result_json():
    return jsonify(z)

@app.route('/data')
def get_data():
    return render_template('data.html')

My JSON:

{
  "data": [
    {
      "first": "Anton",
      "id": 1,
      "last": "Spelec"
    },
    {
      "first": "Rosamunde",
      "id": 2,
      "last": "Pilcher"
    },
    {
      "first": "Vlasta",
      "id": 3,
      "last": "Burian"
    },
    {
      "first": "Anton",
      "id": 4,
      "last": "Bernolak"
    },
    {
      "first": "Willy",
      "id": 5,
      "last": "Wonka"
    }
  ],
  "draw": 1,
  "recordsFiltered": 5,
  "recordsTotal": 5
}

My html page with DataTables inicialisation:

<script>

        $(document).ready(function() {
           $('#table_id').DataTable( {
              "processing": true,
              "serverSide": true,
              "paging": true,
              "pageLength": 10,
              "lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]],
              "ajax": {
               url: 'api/result',
                },
              columns: [
               { "data": "id" },
               { "data": "first" },
               { "data": "last" }
                ]
           } );
        } );

</script>
 <table id="table_id">
                <thead>
                        <tr>
                                <th>id</th>
                                <th>first</th>
                                <th>last</th>
                        </tr>
                </thead>
 </table>

The XHR is here:

Request URL: http://10.10.10.12/api/result?draw=7&columns%5B0%5D%5Bdata%5D=id&columns%5B0%5D%5Bname%5D=&columns%5B0%5D%5Bsearchable%5D=true&columns%5B0%5D%5Borderable%5D=true&columns%5B0%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B0%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B1%5D%5Bdata%5D=first&columns%5B1%5D%5Bname%5D=&columns%5B1%5D%5Bsearchable%5D=true&columns%5B1%5D%5Borderable%5D=true&columns%5B1%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B1%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B2%5D%5Bdata%5D=last&columns%5B2%5D%5Bname%5D=&columns%5B2%5D%5Bsearchable%5D=true&columns%5B2%5D%5Borderable%5D=true&columns%5B2%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B2%5D%5Bsearch%5D%5Bregex%5D=false&order%5B0%5D%5Bcolumn%5D=0&order%5B0%5D%5Bdir%5D=asc&start=0&length=10&search%5Bvalue%5D=asdf&search%5Bregex%5D=false&_=1536075500781

DataTables documentation advises to cast this parameter to an integer and send it back. I found similar question about draw parameter and it supposed the same, but unfortunately I'm not able to make it work. Casting the parameter to integer would not be a problem, I think, but I'm lost in what to do with it next or how to push the raised draw parameter to my JSON.

Thank you.

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

If DataTables is sending a new value for draw to your server-- just read that value and send it back:

@app.route("/api/result")
def result_json():
    return jsonify(z)

Could just become (adjust the code if DataTables sends the values in some other way):

@app.route("/api/result")
def result_json():
    z.update({'draw': request.form.get('draw')})
    return jsonify(z)

I'm not addressing that your code doesn't seem to do anything with filtering or searching, but at least it gives you a starting point to build from.

Update

From the XHR code you pasted-- it looks like DataTables is passing values in via querystrings-- so request.args.get('draw') would be the way to access that draw data-value.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 4, 2018 at 14:26

Doobeh

9,48042 silver badges32 bronze badges

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

1 Comment

culter Over a year ago

Thank you, Doobeh. Now the response for the XHR is everytime 'null'. The "Processing..." is gone, but the sorting, searching still don't work. I've updated the question with request, it it will help.

Joe McCarty · Accepted Answer · 2018-09-04 14:44:19Z

1

The draw parameter is only used by DataTables to ensure that the Ajax returns from server-side processing requests are drawn in sequence by DataTables. To use features like sorting, filtering and paging you will have to set up your own system of querying your data based on the sent parameters that are passed by Datatables when server-side processing is used.

The default parameters are here. You can also include your own custom parameters to that object by manipulating the data object in the ajax call.

answered Sep 4, 2018 at 14:44

Joe McCarty

1451 gold badge2 silver badges8 bronze badges

5 Comments

culter Over a year ago

Hi Joe, so solving the 'draw' issue will not make these features functional? It seems that server-side processing is much more complicated than client-side :(

Doobeh Over a year ago

You need to implement all the relevant filtering-- if you search for 'asdf' in the DataTable search box, it will send that 'asdf' to your server (search_value)-- where you then need to query your database for the rows that match that, and return just those. Currently you're just returning everything, no matter what is specified.

Joe McCarty Over a year ago

That's correct. You'll need to grab the sent parameters and use them to customize a query that will return the data with the filter, order, length and offset that you want. Luckily Datatables passes all of those variables by default in that object, it's just a matter of creating a system by which you can customize your query to return the data correctly. Server-side processing means you're doing all the work of filtering, ordering and paging. All Datatables is doing is sending helpful parameters with the GET or POST.

culter Over a year ago

Thank you both, guys. You've made me sad. I expected, that at least the column sorting, searching and other few features will work without coding. I found this guy (github.com/SergioLlana/datatables-flask-serverside) with his python/flask DataTables project, but it is outdated and the template is not running at all. I'm thinking about to switch to PHP because there is much more projects with DT and PHP. One last question, is it necessary to fix the draw parameter as I asked ? I'm not decided yet, maybe I'll stay with Python/Flask. Thanks.

Joe McCarty Over a year ago

I can't think of a scenario where you would need to worry about the draw parameter. It's used for a case when, for example, you multi-sort a few columns which sends those requests off to the server. Because of the nature of Ajax, you may not get the responses back in the same order they were sent. The draw parameter helps sync-up those requests so that when draw 2 is received before draw 1, Datatables will disregard draw 1. Hope that makes sense.

Collectives™ on Stack Overflow

DataTables server-side processing - echoing back draw() parameter (Python/Flask)

2 Answers 2

Update

1 Comment

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Update

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related