Datatables serverside JSON/Ajax

[Cristina Sig]

Hello everybody,

I have been struggling with this for days and I'd like some help.
I'm working with DataTables plugin and I'm trying to load data related to University's careers from a database and populate the table through Ajax.

The issue is that I don't much about Ajax and JSON and I got this error message:

DataTables warning: table id=tableCareers - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

I checked the JSON on an online JSON Validator and the result is positive so the problem might be on the Ajax part.

This is my code so far.

Model

db.define_table('Degree',
                Field('description','string'),
                format = '%(description)s'
               )


db.define_table('Faculty',
                Field('description','string'),
                format = '%(description)s'
               )


db.define_table('Career',
                Field('name','string'),
                Field('duration (Years)', 'integer'),
                Field('faculty','reference Faculty', requires = IS_IN_DB(db,db.Faculty.id,'%(description)s'),
                Field('degree','reference Degree', requires = IS_IN_DB(db,db.Degree.id,'%(description)s')
                format= ' %(name)s '
               )

Controller (Tool/manageCareer.py)

def manageCareer():
    import json
    rows = db((db.Career.faculty == db.Faculty.id)&(db.Career.degree == db.Degree.id)).select()
    return dict(results=XML(rows.as_json()))

View

<script>
var table;
$(document).ready(function(){
   table=$('#tableCareers').DataTable( {
        'processing': true,
        'serverSide': true,
        'ajax':
         {
             type:'POST',
             url:"{{=URL('Tool', 'manageCareer')}}",
             data:{{=results}},
         },
        'columns': [
            { data: 'Career.name'},
            { data: 'Career.duration' },
            { data: 'Faculty.description' },
            { data: 'Degree.description' },
        ],
      
    });

     });
</script>


<table id="tableCareers" >
   <thead>
       <tr>
          <th>Name</th>
          <th>Duration (Years)</th>
          <th>Faculty</th>
          <th>Degree</th>
       </tr>
    </thead>
    <tbody></tbody>
</table>

[Leonel Câmara]

It's because you're not using the generic.json view and you're also not setting the response.headers content-type to json. I would simply change your function to this which will do everything for you:

def manageCareer():

    rows = db((db.Career.faculty == db.Faculty.id)&(db.Career.degree == db.Degree.id)).select()

    return response.json({'results': rows})

[John Bannister]

@Leonel .. I think this will work well with client side but not serverside datables but may be wrong.

@Cristina:

My understanding of serverside processing is that you will need to have a few more items returned to datatables as outlined in the DT docs for serverside processing.

Your response to the request from DataTables should look something like this for serverside processing:

return dict(draw=sEcho, recordsTotal=iTotalRecords, recordsFiltered=iTotalRecords, data=aaData)

You will also need to move your sorting, filtering and ordering to the server (which is a bit more complicated) but doable.

As said I could be wrong.

BR

John

[Cristina Sig]

Thank you @Leonel, I tried your suggestion and it works but the JSON response replaces the HTML so, I can't see my Datatable only the plain array information from JSON response.
I don't understand if I call the controller wrong or what is happening?

[Cristina Sig]

Thank you @John

yes, I have been checking DT docs and it seems that I need to add that data, I tried it but the sorting and ordering is not working at all.
Do you have any idea or example how to deal with that?

[Jim S]

I believe you are confusing a couple things here.  The code you're using is trying to use a server-side data set, but the 

data:{{=results}},

is using the results passed to the view as the data to display, not the data you're getting back from the ajax call.

I'll try to make some time later this morning to build up a demo for you.  I've been working with datatables.net and web2py A LOT in the past few weeks and have some good working samples, but will need to tear some of it down to the bare essentials to make what I have more understandable.

Gotta run but will get back to this in a few hours.

-Jim

[Cristina Sig]

Thank you @Jim. I will appreciate if you can share some example because I have been struggling with this for weeks and I'm really confused with ajax and server side.

[Jim Steil]

I've attached the 'packed' file of the app.

The app:

-uses your table setup (I took the uppercase characters out of your table names - I'm not preaching, but in my experience you don't want to do this)
-puts some test data into the degree table
-added a page to list the degrees using datatables.net

Modified files
-added DataTables directory to /static
-views/layout.html - added references to datatables css and javascript files
-controllers/default - added degrees and _get_degrees methods
-created views/default/degrees.html

To Run it
-Extract the code to your applications directory
-fire up web2py
-navigate to 127.0.0.1:8000/school/default/degrees

You should see the list of degrees.  Paging should work and the search filter should work.  I didn't add the 'click on the column heading to sort', but you can do all of that as well.

Let me know if you have any problems or questions.

-Jim

 web2py.app.school.w2p

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/CZtFA49nJ18/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/7e0bcbc2-6f9e-499d-bef5-dee93fd6370f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Cristina Sig]

Thank you @Jim, I will test it out this evening and let you know if I have any questions or doubts about it :)

To unsubscribe from this group and all its topics, send an email to web...@googlegroups.com.

[Cristina Sig]

@Jim

I tried the app and it works!! but I have a question, is it possible to search through all the columns or has to be one at the time?
Also, do you know if this server side method works on data bases with thousands of records or is it a bit slower?

Thanks again!

To unsubscribe from this group and all its topics, send an email to web...@googlegroups.com.

[Jim Steil]

Yes, you can search through multiple columns. I just happened to write the sample on a table with one column. The key is in building the DAL query properly.

Jim

To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/97f23c06-182d-405d-b73c-13cf1f93ebd8%40googlegroups.com.