Load JSON from Django Variable (Ajax)

a_adams

Hi All,

I've been trying to get my DataTable to load data using Ajax and I haven't been able to get the JSON loaded from my Django variable into the DataTable, despite validation that the JSON is correctly formatted. I had originally gotten the table running without Ajax, loading the data through django variable loops, but I want to implement Ajax to use data loading functionalities for large sets of data etc.

Here is the script / html:

             <table id="test_table" class="display table table-striped table-bordered" border="1" cellspacing="0" width="100%">
                <thead>
                  <tr>
                    <th>First</th>
                    <th>Last</th>
                    <th>Num.</th>
                  </tr>
                </thead>
                         
              </table>
              <script type="text/javascript">
                $(document).ready(function() {
                    
                    var json={{ test_data | safe }}
                    var data= json["data"]
                    var json2 = JSON.stringify(data)
                    window.alert(json2)
                    $('#test_table').DataTable({
                      "ajax": json2    
                    } );

                });
                    
              </script>

The JSON for json2 is the following:

[{
    "first_name": "John",
    "last_name": "Smith",
    "number": "12345"
}, {
    "first_name": "Alex",
    "last_name": "Smith",
    "number": "122345"
}, {
    "first_name": "John",
    "last_name": "Smith",
    "number": "12345"
}, {
    "first_name": "Alex",
    "last_name": "Smith",
    "number": "122345"
}]

Here is my view for the data:

    def test_data(request):
        test_all = TestData.objects.all().values('first_name', 'last_name', 'number')
        test_all = json.dumps({"data": list(test_all)})
        data = {'test_data': test_all,}
        return render(request, 'test.html', data)

Might anyone be able to help here? I've tried implementing the dataTable with ajax using a few methods ("data" field and "column" values, etc.), but I always hit the blocker message DataTables warning: table id=test_table - Invalid JSON response and the dev tools don't give me a reason as to why the JSON didn't load. The ultimate goal is to be able to use server side processing (to enable deferLoading etc.)

Thank you so much!

a_adams

.

kthorngren

Since you are using an array of objects you need to use columns.data to define your Datatables data structure.

Kevin

a_adams

Hi Kevin, thank you for the response! My initial attempt was using columns.data, however I was receiving the same error mentioned (DataTables warning: table id=test_table - Invalid JSON response).

For example, using the following format:

$('#test_table').DataTable({
                          "ajaxSource": json2,
                            "columns": [
                              { 
                                "data": "first_name",
                                
                              },
                              { 
                                "data": "last_name",
                                
                              },
                              {
                                "data": "number",
                                
                              }
                            ]
                        } );

I receive the error and when looking at dev tools, the output request URL is > > > `

/test/[%7B%22first_name%22:%22John%22,%22last_name%22:%22Smith%22,%22number%22:%2212345%22%7D,%7B%22first_name%22:%22Alex%22,%22last_name%22:%22Smith%22,%22number%22:%22122345%22%7D,%7B%22first_name%22:%22John%22,%22last_name%22:%22Smith%22,%22number%22:%2212345%22%7D,%7B%22first_name%22:%22Alex%22,%22last_name%22:%22Smith%22,%22number%22:%22122345%22%7D]?_=1522114138525 HTTP/1.1" 200 12720

I'm not sure I understand the encoded data URL?

I also implemented the columns.render option, and received the same encoded URL results.

Am I missing something simple?

Best,
Alex

kthorngren

It looks like you are not returning the JSON in an object called data which is the default Datatables looks for. In that case you would use ajax.dataSrc.
The second example on the doc page is what I think you will want to use.

It looks like your JSON string is escaped. I've had that problem when I double encode the data into JSON. For example the AJAX called function calls another which encodes the return as JSON. Then the original function encodes that same data as JSON which results in an escaped string.

Kevin

a_adams

Hi Kevin, thanks for getting back to me again. With regards to the JSON encoding, is the only solution to rework how the data is sent from the Django view linked above to the page maybe, or can decoding be executed in the html script? Unescaping functions didn't result in any changes, also after implementing ajax.dataSrc - and parsing simply returned [Object object] for each entry which doesn't help. How were you able to fix it?

Thanks!
Alex

kthorngren

In Python I use json.dumps(data) for the response data. Not sure if its the same in Django. Just make sure you are doing this only once in your Django code.

If you want you can post your Django code and I'll take a look but I don't use Django but do use Python and Cherrypy.

Kevin

a_adams

Hi Kevin,

Thanks for confirming that - I am doing the same thing. The views file function linked below is what grabs the data from the DB, puts it into the variable 'data', which I access in the html page file seen below:

Views function that generates variable to be accessed on page:

def test_data(request):
    test_all = TestData.objects.all().values('first_name', 'last_name', 'number')
    test_all = json.dumps({"data": list(test_all)})
    data = {'test_data': test_all,}
    return render(request, 'test.html', data)

Current dataTable example using the variable outputted from above:

$(document).ready(function() {
                  
                    var json={{ test_data | safe }}
                    var json2 = JSON.stringify(json)
                    $('#test_table').DataTable({
                       "ajax": {
                            "url": json2,
                            "dataSrc": "data"
                          },
                        "columns":[
                        {"data": "first_name"},
                        {"data": "last_name"},
                        {"data": "number"}]
                        } );

                });

Data in the variable json2:

{"data":[{"first_name":"John","last_name":"Smith","number":"12345"},{"first_name":"Alex","last_name":"Smith","number":"122345"},{"first_name":"John","last_name":"Smith","number":"12345"},{"first_name":"Alex","last_name":"Smith","number":"122345"}]}

Thanks!
Alex

kthorngren

I think its becoming more clear - sorry a bit slow on this one I'm assuming you are still having the same invalid JSON response error.

Your AJAX URL is "json2" which is a Javascript variable. That won't work. AJAX is expecting to send an HTTP request and receiving a response containing the JSON data. I'm not sure what this line is actually doing var json={{ test_data | safe }} but I'm guessing its a Django way to call your test_data() function and assign the value to the variable "json".

The little bit of Django docs I looked at indicate the third parameter in the Django render() function is a dictionary. Didn't see reference to this but I suspect that Django will automatically convert the dict to JSON and return to the web page. If this is the case then your "test_all" data is converted to JSON twice, which I think is the problem, and you will want to remove the json.dumps() function call. I'm thinking you will want your Django function to look like this:

def test_data(request):
    test_all = TestData.objects.all().values('first_name', 'last_name', 'number')
    return render(request, 'test.html', {"data": list(test_all)})

This assumes render() will convert the dictionary to JSON.

Since it seems that "json" is receiving the response from test_data() it might be easier to use Datatables data option rather then ajax. If my above assumptions are correct then I think you should change you JS code to look like this:

$(document).ready(function() {
                   
                    var json={{ test_data | safe }}
                    var json2 = JSON.parse(json)  //convert json data to JS object
                    $('#test_table').DataTable({
                        data: json2.data,   //get the array of object data
                        "columns":[
                        {"data": "first_name"},
                        {"data": "last_name"},
                        {"data": "number"}]
                        } );
 
                });

I wonder if this would work:

$(document).ready(function() {
                   
                    $('#test_table').DataTable({
                        ajax: {{ test_data | safe }},   //get the data from test_data
                        "columns":[
                        {"data": "first_name"},
                        {"data": "last_name"},
                        {"data": "number"}]
                        } );
 
                });

Let us know the results.

Kevin

a_adams

Hi Kevin,

No worries at all - I really appreciate the help here! So you are correct about the django behavior - the function returns a dictionary that I can access through the following variable : {{ test_data | safe }}

I played around with your suggestions, and you were definitely correct - I was encoding the JSON twice. I ended up just using json.dumps in the django function:

test_all = TestData.objects.all().values('first_name', 'last_name', 'number')
    test_all = json.dumps({"data": list(test_all)})
    data = {'test_data': test_all,}
    return render(request, 'test.html', data)

and your suggested javascript without another json encoding:

var json={{ test_data | safe }}

                    $('#test_table').DataTable({
                       data: json.data,   //get the array of object data
                        "columns":[
                        {"data": "first_name"},
                        {"data": "last_name"},
                        {"data": "number"}]
                        } );

and I am now seeing data load!

My last question here is that my initial goal was to be able to load a large set of data, one column containing images, using deferLoading (which I understand uses the serverSide variable). I also thought using the function above data: json.data does not allow this - in testing it was non-functional as well. What is my best bet for next steps forward?

Thank you so much for your help again.

Best,
Alex

kthorngren

Enabling server side processing moves the client side features like searching and sorting to the server script. Your Python scripts will need to support the parameters sent to the server then query the DB using things like LIMIT and OFFSET to retrieve and respond with a page worth of data. This happens for each draw of the table (search, sort, page, etc). The server side processing explains this.

Yes, you will need the ajax option for server side processing. Did you try the second JS option using ajax?

The deferRender option might be a good option for you to stay with client side processing. Take a look at this FAQ. If you are going to use columns.render to display images, etc you will want to make sure you only render for the "display" type. The orthogonal data doc explains the different types.

Stick with client side processing if you can. Its much simpler.

Kevin