Datatable with ajax call

dukiss

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

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

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

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

kthorngren

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

Kevin

dukiss

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

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

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

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

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

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

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

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

Gong between Python syntax and Javascript is lots of fun

Kevin