CODEWITHSUNDEEP
jquery-pluginsdatatableweb2py
Vineet
Vineet

Reputation: 11

JSON data for jQuery dataTable in web2py

I am trying to load json data from web2py controller to jQuery dataTable via AJAX.
But only blank dataTable is rendered (with desired formatting, search box, etc.)
Can somebody pl. point out into my code as to where I have a mistake.
Data is not displayed (as returned by "get_data" method).
I have made sure that the database tables have been populated.

Controller

def show_data():
    return dict()

def get_data():
    custdata = db.executesql(qry, as_dict=True)
    return custdata

For testing purpose, I returned response.json(custdata) in a separate method & validated the same on "jsonlint.com". It is valid json.

View (show_data.html)


{{extend 'layout.html'}}
$(document).ready(function() {
    var oTable = $('.smarttable').dataTable( {
        "sScrollY": "200px",
        "sAjaxSource": "{{=URL('MIS','get_data.json')}}",
        "sDom": "frtiS",
        "bDeferRender": true
    } );
} );

Lastly, html table tags are defined for a table with class="smarttable"

Upvotes: 0

Views: 3857

Answers (2)

kommradHomer
kommradHomer

Reputation: 4210

you have to have the "key" values in the "dictionary" that you give for return .

iTotalRecords,iTotalDisplayRecords,sEcho and aaData. you can find the explanations in http://datatables.net/usage/server-side

Upvotes: 0

Anthony
Anthony

Reputation: 25536

Your get_data function should return a dictionary, like this:

def get_data():
    custdata = db.executesql(qry, as_dict=True)
    return dict(data=custdata)

In web2py, a view is only called if the controller action returns a dictionary -- otherwise, the controller output is simply returned to the client as is (and as is, custdata has not yet been converted to JSON).

When you call the URL /MIS/get_data.json, the .json extension tells web2py to look for a /views/MIS/get_data.json view file to use for rendering the JSON. If it doesn't find that view file, it will trying using /views/generic.json, though it will only use generic.json for local requests, unless you override that by specifying response.generic_patterns=['json'].

As an alternative to using a view to render the JSON, you could also do:

def get_data():
    custdata = db.executesql(qry, as_dict=True)
    return response.json(custdata)

EDIT: The jQuery DataTables plugin requires the JSON to include some special parameters, so you'll have to add those before returning the JSON. To make things easier, you might consider using PowerTable (a web2py plugin for DataTables), or the jqGrid widget included with web2py's plugin_wiki (the widget can be used on any web page, not just wiki pages).

Upvotes: 1

Related Questions