Pagination with server-constructed MySQL query and AJAX

nat5142

I'm using Flask and sqlalchemy to create a query that contains a constant LIMIT of 100 and a varying OFFSET parameter to select different data results via AJAX. I haven't implemented DataTables yet because I can't quite understand how the built-in page buttons located below the DataTable communicate with the server to load new data results. For example, when page 2 of a 100-record-per-page DataTable is selected, I assume that the first 100 records in the JSON are skipped, and results 101-200 are displayed in the view. But what if I intend to implement AJAX and a new query (with a different OFFSET value) needs to be constructed and executed each time a page button is clicked, and a new set of results are to be sent back to the client from the server?

I'm working with a database containing over 107 million records, so returning full queries and paginating all results isn't really an option.

kthorngren

This page discusses server side processing:
https://datatables.net/manual/server-side

Please post any further questions you have.

Kevin

nat5142

@krthorngren yeah, I've parsed over that and I'm just not sure as to how to tell the server, "hey, the user wants to load page 3. The offset value should be 299."

I.e. when the page is selected, does DataTables send a "draw" parameter to the server that is equal to the page number? And if so, I assume I will need to construct my returned data in a format that reflects the "draw" value?

bindrid

Use start to point to the first row and length to point to the last row and grab what is there.

The draw value that gets passed in should be passed back unchanged, it helps to keep the ajax calls in sync.

All modern databases provide a way to number the result set from row 1 to whatever in the select statement (not meaning the primary key).
That allows you to use the data table start value as the first row and the length as the last value to determine the page.

There are a ton of sql examples out there to show you how.

kthorngren

To expand on Binrid's answer....

You can look at the server side processing examples to see how this works. Take the simple example:
https://datatables.net/examples/server_side/simple.html

Open the Dev tools and go to the network tab of your browser. Click on a page and look at the request sent. There is a "draw" parameter which is like a sequence. There is are "start" and "length" which provide the range of records to get. For example clicking page 3 "start" is 20 and "length" is 10. This is based on displaying 10 rows. Also within the parameters will be search and ordering information.

I haven't used sqlalchemy but it looks like there are .limit() and .offset() methods that can be used to control your paging. It looks like these need to be applied after you apply your filters. Not sure though.

Kevin

nat5142

@kthorngren Thanks for the tips, Kevin. I'm still a little confused about how the "start" and "length" values are communicated with the server. Are they created dynamically as each page button is clicked?

I clicked page 2 and under Network -> Headers -> Query String Parameters in the Developer tools I see this. I don't quite understand what this means. Is this the parsed request that's being sent to the server? If so, I think I understand how I can use the start and length parameters to implement them into .limit() and .offset() within sqlalchemy.

Nick

nat5142

@bindrid thanks for the help, I'll definitely take that into account

kthorngren

Yes, that is the request to the server. When you do things like paging or searching Datatables will send a new request to the server with the parameters you see. If you are allowing searching then you will also need to incorporate that into your sqlalchemy queries.

The SSP doc provides definition of the parameters sent to the server and the specs for what it expects to receive from the server.

Fortunately all my Python based Datatables are client side processing :-) Good luck and keep posting questions.

Kevin

nat5142

@kthorngren If I'm going to be returning ~1000 rows of data to display in my DataTable, do I need to manually assemble a variable in JSON object form containing draw, recordsTotal, recordsFiltered, data and error parameters? Or will that be done for me by DataTables? Don't quite understand what DataTables will require on either end of an ajax POST request.

Would I be required to do something like this to pass data back to ajax in proper format?

in views.py: (in arbitrary view where methods=["POST"])

ajax_request = request.json
start = ajax_request['start'] #OFFSET value for query
length = ajax_request['length'] #LIMIT value for query

query = #some query that incorporates start and length as OFFSET and LIMIT

#   #   #   #   #   #   #   #
# alter query results to make them JSON serializable here
#   #   #   #   #   #   #   #

query_results = [{"key1":"foo", "key2":"bar"},
        {"key1":"hello", "key2":"goodbye"}, 
        ... ] #query results in JSON serializable form 

recordsTotal = "SELECT count(*) FROM table"
recordsFiltered = len(query_results)
response = {"draw": ajax_request['draw'], "recordsFiltered": recordsFiltered, 
    "recordsTotal": recordsTotal, "data": query_results, "error": someErrorVariable}
return jsonify(response)

Not sure if this will matter, but I'm using MySQL as my database

kthorngren

The above looks reasonable except recordsFiltered. The value is the total number of records after filtering, not the number your are returning. Since it doesn't look like you are applying filtering this would be the same as recordsTotal.

The DB doesn't matter to Datatables but I use MySql too. I'm not doing server side nor using sqlalchemy. The MySql library I use returns a list of dictionaries which contain the
records. I can process if needed then I return with json.dumps( { 'data': records} ). Your pseudo code looks to do the same.

I don't think you want to include error unless there is an error.

Not sure if this was mentioned above but with server side processing enabled the sorting and search functions are performed by the server side scripts. I would start with your above pseudo code then once working add on searching and sorting.

Kevin

nat5142

@kthorngren right, I actually patched up the recordsFiltered variable after I posted this. I created a new function in my models.py file which selects the count of all records in the table after the filter is applied.

A few new questions:

When the user enters a set of parameters and clicks the submit button for the form, the data is sent to one route, and the form input fields are stored in a Flask session. Then, I'll create a second route which accepts a new ajax request from DataTables, queries the database, and returns the query results according to the "start" and "length" DataTables will send to the server.

Problem is I don't know what to return from the first route because there isn't any data coming back that needs to be processed on the client-side, just stored on the server-side:

views.py:

@app.route('/params', methods=['POST'])
def params():
    params = request.form
    form = dict((k,v) for k,v in params.items() if v)
    session['form'] = form
    # function call to get right number of recordsFiltered goes here
    session['recordsFiltered'] = recordsFiltered

    return ???      #can I just use "return True" here?

@app.route('/table', methods=['POST'])
def table():
    form = session.get('form', None)
    recordsFiltered = session.get('recordsFiltered', None)

    #   #   #   #   #   #   #   #   #   #   #
    # here's where the code from my last comment will go
    #   #   #   #   #   #   #   #   #   #   #

    return jsonify(response)

In addition to not knowing what to return from the first route, I only want the DataTable to be drawn after the submit button is pressed and the data is properly collected and returned. Here's the jQuery I have so far:

$(document).ready(function() {
    $('.submit_button').on('click', function(event) {
        $.ajax({
            url: '/params',
            data: $('form').serializeArray(),
            type: 'POST',
            success: ????????
                // If there's no data to return from the
                // '/params' route, what goes here?
                // Is this where I call the DataTables instance?
        })
    });
    // or do I call DataTables here? Will jQuery
    // work sequentially and only display the
    // DataTable after submit if I do it this way?
});

Sorry, I know this might be a lot to process. Still trying to get a grasp on JavaScript

kthorngren

Hmm, need to think about that one a bit. The first form you are using is outside of Datatables. The user will provide some parameters which will influence the SQL query. Then you want to display with Datatables. Someone more familiar with server side processing can help answer how that process would work generically.

In trying to figure out if you need server side processing I have a couple questions:

• When the user interacts (search / sort) with the Datatable is that with the original dataset returned or with the full DB stored on the server?
• If the user needs a new dataset is that performed with the original form or through Datatables search functionality?

The reason I ask is if the user gets the dataset with the original parameter form then only interacts with the returned dataset then you can use client side processing which is much easier. You can use your parameter form to get a new dataset, clear Datatables then refresh Datatables with the new data.

Kevin

nat5142

@kthorngren

Yeah, your first paragraph is right on track. That's exactly what I'm trying to build.

So my data is a collection of meteorological observations from stations located across the United States. The data is collected in 5-minute intervals, so for one given station, there are 288 data points for each day. I have a form element available for the user to input time constraints, and I feel as though searching by station ID (i.e. KPHL for Philadelphia) and limiting the search to a small window of time would be the most appropriate use for the site.

I actually hadn't considered how to handle search and sort until now, but to be honest that's fairly low on my list of priorities at the moment. Searching and sorting will be very simple if the user is limiting the records to < 1 page of data (less than 1000 records), but outside of that it gets a little tricky, I'm now realizing. Currently I think search and sort functionality is something I'll address after I get the DataTable working properly. If it can't be implemented for the Table, it's really not the end of the world for me.

I'm pretty much positive that I'll need to implement server-side processing, as a simple query with only one entered parameter (such as the station identification - KPHL), will return over 10,000 records. Loading times for these big queries are phenomenally slow if I don't break them up into increments.

If the user needs a new data set they can just re-enter new query parameters in the form which will remain above the DataTable. That's at least how I have the site working at the moment.