Datatable with ajax call

dukiss · March 2022

Hello . Im new to Datatables and I've created a very simple django application that handles books. I can project that data into a datatable with a {%for%} loop but Im having trouble figuring out the way it works with ajax calls .
my view :

def index(request):
    bookcollection = Book.objects.all()
    return render(request, "books/index.html",{ "bookcollection" : bookcollection})

index.html

{% block content %}

<br>
<br>
<div class="container">
    <table id="books" class="display" style="width:100%">
        <thead>
            <tr>
                <th>Book</th>
                <th>Author</th>
                <th>Genre</th>
                <th>Date Published</th>
                <th>Copies</th>
            </tr>
        </thead>
        <tbody>
             {% for book in bookcollection %}
            <tr>
                <td>{{book.name}}</td>
                <td>{{book.author}}</td>
                <td>{{book.genre}}</td>
                <td>{{book.pub_date}}</td>
                <td>{{book.copies}}</td>
            </tr>
            {% endfor %} 
        </tbody>
    </table>

</div>
{% endblock content %}

{% block javascript %}
<script>

    $(document).ready( function () {
        $('#books').DataTable();
    } );
{% endblock javascript %}

So the question is : How do i do the same thing (fetch the data from the database and project it on a datatable) with ajax call? .The examples shown in the website are from a json file so I cant figure it out. I know I have to use this function but I cant get through it.

$('#myTable').DataTable( {
    ajax: {
        url: '...',
    },
    columns: [...]
} );

rf1234 · March 2022

I am using Editor but if you want to emulate what Editor returns to the client it is fairly simple.

I have a table called "sub_exec_cashflow"

This could be the SELECT statement:

SELECT CONCAT("row_", id) as DT_RowId,
       ctr_id, cashflow_due_date, cashflow_amount
FROM sub_exec_cashflow
WHERE ctr_id = <whatever you posted to the server>

Subsequently you "json_encode" the selected associative array and return it to the server. "DT_RowId" is something that always needs to be the first variable to return. It is not more than the prefixed id of the table you are reading.

Something like this could be your Javascript:

var table = $('#myTable').DataTable( {
    dom: "Bfrltip",
    ajax: {
        url: 'your server script with the SQL statement',
        type: 'POST',
        data: function ( d ) {            
            d.ctr_id = "the parent id, if any"
        }
    },
    columns: [
        {   data: "ctr_id" },
        {   data: "cashflow_due_date" },
        {   data: "cashflow_amount" },
    ],
    columnDefs: [
        // targets may be classes
        {targets: [0], searchable: false}
    ],
    order: [[ 1, 'desc' ]],
    select: {
        style: 'single'
    },            
    buttons: [   ]
} );

dukiss · March 2022

Is there a way to do this without any SQL editing? just views.py and js editing.
I used your JS sample and I got up to the point where I get to the datatable but it doesnt print any of the values inside.

rf1234 · March 2022

You could use Editor. Then you don't have to write any SQL yourself.

kthorngren · March 2022

Take a look at this tutorial showing how to return JSON data using Django.

Kevin

dukiss · March 2022

I tried using this this way from the tutorial :
view :

def getbooks(request):
    print("DONE")
    if  request.method == "GET":
        books= Book.objects.all().values()
        bookserial = list(books)
        return JsonResponse(bookserial, safe=False)
    return JsonResponse({'message':'error'})

and I tried these ways :

    var table = $('#books').DataTable( {
        ajax: {
            url: "{% url 'getbooks' %}",
            type: 'GET',
        
        },
        columns: [
            {   data: "book.name" },
            {   data: "book.author" },
            {   data: "book.genre" },
            {   data: "book.pub_date" },
            {   data: "book.copies" },
        ],
    } );

    $.ajax({
        type: "GET",
        datatype : 'json',
        url: '{% url 'getbooks' %}'
        success: function (response) {
         
            response.forEach(obj => {
                var option = new Option(obj['fields']); 
                $('#books tbody').prepend(
                    `<tr>
                        <td>${fields["book.name"]||""}</td>
                        <td>${fields["book.author"]||""}</td>
                        <td>${fields["book.genre"]||""}</td>
                        <td>${fields["book.pub_date"]||""}</td>
                        <td>${fields["book.copies"]||""}</td>
                        </tr>`
                )
            });
        },
        error: function (response) {
            alert(response["responseJSON"]["error"]);
        }
    });

both separately and together but all I get is the JSON list from the view. Any ideas?
Also note that im not trying to do it with a {%for%} as the first example just plain ajax. Thanks

allan · March 2022

Could you give me a link to your page please? I don't quite understand what you mean that you get the JSON list. Is that not what you want?

Thanks,
Allan

kthorngren · March 2022

You have the jQuery ajax request in line 16 that looks to be populating the table. Plus you have the ajax option in the Datatables config that is using the same HTML table. You will want to remove the ajax request in line 16 so you don't have competing code filing the table.

Use the browser's network inspector tool to see what is actaully returned from the Python script. Use the steps in this technote.

Doesn't look like you are placing the JSON data in the data object (dictionary in Python terms) that Datatables looks for by default. If not then you will need to use the ajax.dataSrc option. I think you will want to use the 2nd example in the docs.

Kevin

dukiss · March 2022

I'm sorry for troubling you I'll try to explain it better . In my very first example it goes with a {%for%} loop and prints the data from the database. Now I want to remove that table body so it will be :

 <table id="books" class="display" style="width:100%">
        <thead>
            <tr>
                <th>Book</th>
                <th>Author</th>
                <th>Genre</th>
                <th>Date Published</th>
                <th>Copies</th>
            </tr>
        </thead>
        </table>

I used this view :

def getbooks(request):
    print("DONE")
    if  request.method == "GET":
        books= Book.objects.all().values()
        bookserial = list(books)
        return JsonResponse(bookserial, safe=False)
    return JsonResponse({'message':'error'})

and I got this result with some models for reference :
So what is the correct ajax call or function that prints this list into a datatable? Im pretty sure its 1-2 lines of code that im getting wrong and i just cant figure it out .
Thanks and again sorry for making such a big fuss

kthorngren · March 2022

Looks like your row data is not in the data object as I described above. Did you try the ajax.dataSrc option I suggested?

Kevin

dukiss · March 2022

yeah i tried it both like

 $(document).ready(function() {
        $('#books').DataTable( {
        ajax: {
            url: "{% url 'getbooks' %}",
            type: 'GET',
            dataSrc: "",
    
            columns: [
                {   data: "name" },
                {   data: "author" },
                {   data: "genre" },
                {   data: "pub_date" },
                {   data: "copies" },
            ],
        };
    });
} );

and without the columns or some other parameters.I still get the same result as the above image

kthorngren · March 2022

You have the columns inside the ajax option. Line 15 has a ; which is a syntax error.

Try this:

$(document).ready(function() {
        $('#books').DataTable( {
        ajax: {
            url: "{% url 'getbooks' %}",
            type: 'GET',
            dataSrc: "",
        },     
            columns: [
                {   data: "name" },
                {   data: "author" },
                {   data: "genre" },
                {   data: "pub_date" },
                {   data: "copies" },
            ],
    });
} );

Kevin

dukiss · March 2022

Yeah thanks a lot that did the trick i just had syntax errors
I'll try to be more careful thanks for your help!

kthorngren · March 2022

Gong between Python syntax and Javascript is lots of fun

Kevin

Datatable with ajax call

Datatable with ajax call

Answers

Howdy, Stranger!

Categories

DataTables

Datatable with ajax call

Datatable with ajax call

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables