JSON data for jQuery dataTable in web2py

Question

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"

Anthony · Accepted Answer · 2011-06-26 04:10:46Z

1

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).

edited Jun 26, 2011 at 4:10

answered Jun 25, 2011 at 15:05

Anthony

25.5k3 gold badges31 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Vineet Over a year ago

I had already tried to return dict(data=custdata) in "controller". Then in "View", tried to access that data as:--"sAjaxSource": "{{=URL('MIS','get_data')}}", but no success. If response.json(custdata) is used, and accessed directly in the browser, it writes json on the browser page. But if accessed from "sAjaxSource", no data is rendered. I think there is something wrong in the way I am calling "get_data" method. Can you pl. help to find the mistake?....Thanks.

Anthony Over a year ago

Is MIS your app name or the controller name? URL() will assume it's the controller name and generate the URL as /appname/MIS/get_data -- is that what it's supposed to be, or should it be /MIS/controller/get_data (in that case, you need to replace 'MIS' with the controller name in your call to URL()).

Vineet Over a year ago

I have the structure like this. a='MyWheels', c='MIS', f='get_data'. So, {{=URL('MIS','get_data')}} should work. But it is not returning the data into dataTable.

Anthony Over a year ago

Looks like DataTables requires your JSON to include a few special parameters (see datatables.net/usage/server-side), so you'll need to add those and format the JSON as required before returning it.

kommradHomer · Accepted Answer · 2011-07-27 14:49:46Z

0

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

answered Jul 27, 2011 at 14:49

kommradHomer

4,2295 gold badges55 silver badges72 bronze badges

Collectives™ on Stack Overflow

JSON data for jQuery dataTable in web2py

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related