Datatable with ajax call

Datatable with ajax call

dukissdukiss Posts: 6Questions: 1Answers: 0

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: [...]
} );

This question has an accepted answers - jump to answer

Answers

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    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: [   ]
    } );
    
  • dukissdukiss Posts: 6Questions: 1Answers: 0

    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.

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited March 2022

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

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735

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

    Kevin

  • dukissdukiss Posts: 6Questions: 1Answers: 0

    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

  • allanallan Posts: 61,435Questions: 1Answers: 10,049 Site admin

    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

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735

    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

  • dukissdukiss Posts: 6Questions: 1Answers: 0
    edited 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

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735

    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

  • dukissdukiss Posts: 6Questions: 1Answers: 0
    edited 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

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735
    Answer ✓

    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

  • dukissdukiss Posts: 6Questions: 1Answers: 0

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

  • kthorngrenkthorngren Posts: 20,139Questions: 26Answers: 4,735

    Gong between Python syntax and Javascript is lots of fun :smile:

    Kevin

Sign In or Register to comment.