dataTables with serverside json

[Vineet]

@Richard & any other web2py soul interested in dataTables with
serverside json,

This refers to the post by Richard Vézina --

https://groups.google.com/group/web2py/browse_thread/thread/f8e6bedbfd457160/00b3b1fbf91b1319?hl=en&lnk=gst&q=weekend+vineet#00b3b1fbf91b1319

At last, I got time to tinker with datatables & json.
I can now successfully request server side script to return json
output.

If you are still looking for a solution with your code, maybe I can be
useful to share my findings.

:-)
Vineet

[Richard Vézina]

Revive pretty old thread!!!

Hello Vineet,

I fall on this thread today as I was trying to put serverside to work with datatables...

I also read this threa on stackoverflow : http://stackoverflow.com/questions/6476133/json-data-for-jquery-datatable-in-web2py

Until now here what I come with :

def show_data():

    return dict()

def get_data():

    query='SELECT * FROM v_result_all;' #"(db['v_result_all'].id > 0)"

    iTotalRecords='SELECT COUNT(*) FROM v_result_all;'

    iTotalDisplayRecords = iTotalRecords

    custdata = {}

    custdata['aaData'] = db.executesql(query)

    custdata['iTotalRecords']=db.executesql(iTotalRecords)[0][0]

    custdata['iTotalDisplayRecords']=db.executesql(iTotalDisplayRecords)[0][0]

    custdata['sEcho']=1

    return response.json(custdata)

But when calling show_data view datatables complains about not able to parse the json response...

I am not sure if it because of utf-8 since web2py return special character in unicode (ex.: "\u00b5") or may be because of miss match in the number of column...

Thanks for any help!

Richard

[Bruno Rocha]

BTW. PowerGrid is a plugin wrote with Jquery templates and it is full JSON/PJSON based http://labs.blouweb.com/PowerGrid

--

Bruno Rocha

[http://rochacbruno.com.br]

[Richard Vézina]

Hello Bruno,

Thanks to notice... I would prefer to not have dependencies... I will look into your code to try to understand why I can't have DTs to parse my json response...

Richard

[Richard Vézina]

Hey Bruno,

Is PowerGrid base on DTs?

PowerTable was but I can't find any trace of DTs in PowerGrid...

Thanks.

Richard

[Bruno Rocha]

Not,

PowerGrid is based in pure Jquery Templates

[Richard Vézina]

Finally got it to work...

There was a problem with the init of DTs particularly this option :

http://datatables.net/release-datatables/extras/FixedColumns/server-side-processing.html

fnInitComplete

It's not solving my speed problem as I expect it could do... So I will put it on hold, but I plan to implement a basic server interaction script as the php DTs example to make it easier deploy DTs with server side capability...

Richard

[Vineet]

Hi Richard !
Pl. excuse my late replying.
Glad to hear that you got it working.

If you are interested, I can share my code.
I am not using "fnInitComplete".
Mine is very simple & minimal code.

--- Vineet

On Feb 7, 10:19 pm, Richard Vézina <ml.richard.vez...@gmail.com>
wrote:

> Finally got it to work...
>
> There was a problem with the init of DTs particularly this option :
>

> http://datatables.net/release-datatables/extras/FixedColumns/server-s...
>
> *fnInitComplete*
> *
> *
> *
> *

[Richard Vézina]

Yes!

I think the PowerTable of Burno don't implement server side processing yet... We maybe can start form there to make a server side implementation of PowerTable... What do you think?

I think, we have to split table header from data then wrote a python implementation fo the php script as shown in the example of DTs...

For sure see a bit of your code could help me figure out the path I should follow and help my reflexion.

About "fnInitComplete" it is just needed in case you want to initialise DTs with fixed columns...

Richard

[Vineet]

Richard,
Here is my minimal code for returning json data in dataTables via
web2py's ajax function.

---View---
<!-- here's a well-formed table -->
<table id="abc_table">
<thead>
<tr>
<th>ABC Category Name</th>
<th>Code</th>
</tr>
</thead>
<tbody>
</tbody>
</table>

<script>
// this I keep in js folder

function dtbl_show(hash_div, sAjaxSource, aoColumns) {

$(document).ready(function() {

$(hash_div).dataTable( {

"bJQueryUI": true,

"bDeferRender": true,

"bPaginate": true,

'sPaginationType': 'full_numbers',

"bProcessing": true,

"bServerSide": true,

"aaSorting": [[ 1, "desc" ]],

"bAutoWidth": false,

"aoColumns" : aoColumns,

"sAjaxSource": sAjaxSource

} );

} );

jQuery.fn.dataTableExt.oPagination.iFullNumbersShowPages = 10;

}
</script>

<script>
// call here this script & pass the table's div for rendering it

dtbl_show("#abc_table", "{{=URL('abc','get_abcdata')}}", [{ sWidth:
'350px' }, { sWidth: '200px' }] )
</script>

--- End View ---

--- Controller for populating data via ajax ---
(excuse the bugged-up indentation here)

def get_abcdata():
if request_vars_iDisplayStart != None:

iDisplayStart = request_vars_iDisplayStart

else:

iDisplayStart = 0



if request_vars_iDisplayLength != None:

iDisplayLength = request_vars_iDisplayLength

else:

iDisplayLength = 10



if request_vars_sEcho != None:

sEcho = int(request_vars_sEcho)

else:

sEcho = 1


qry = 'your sql query string'


### Below this, I am using a 3rd party library viz. DABO for
interacting with DB.
### You may substitute that syntax with the DB layer of your choice
(DAL or whatever else) and get dataset.

try:

conn_name = connInstance.makeConn()

cur = conn_name.cursor()

qry = qry1 + ' limit ' + str(iDisplayStart) + ', ' +
str(iDisplayLength) + ';'

# fetch total data

cur.execute(qry1)

data_full = cur.getDataSet()

iTotalRecords = len(data_full)

# fetch data as requested from client

cur.execute(qry)

data_disp = cur.getDataSet()

iTotalDisplayRecords = len(data_full)

finally:

conn_name.close()


aaData = []
# Now populate the aaData with data in form of lists.
# e.g. aaDada will look like -- [[1, 'Vineet'],[2,'Richard']]
# This formatting is important

D=dict(sEcho=sEcho, iTotalRecords=iTotalRecords,
iTotalDisplayRecords=iTotalDisplayRecords,
iDisplayLength=iDisplayLength, aaData=aaData)
return response.json(D)

-- End of Controller code --

That's wraps it up.
If you need any further drilling down in my code, pl. ask.
I will be happy to share.

HTH,
--- Vineet

On Feb 8, 10:18 pm, Richard Vézina <ml.richard.vez...@gmail.com>
wrote:

> Yes!
>
> I think the PowerTable of Burno don't implement server side processing
> yet... We maybe can start form there to make a server side implementation
> of PowerTable... What do you think?
>
> I think, we have to split table header from data then wrote a python
> implementation fo the php script as shown in the example of DTs...
>
> For sure see a bit of your code could help me figure out the path I should
> follow and help my reflexion.
>
> About "fnInitComplete" it is just needed in case you want to initialise DTs
> with fixed columns...
>
> Richard
>

[Richard Vézina]

Hi Vineet,

Thanks for sharing. So, if I understand correctly, you use DABO code as an equivalent of the php code in the example from the DTs to allow grid to interact with database...

I will have a look at PowerTable this week to see if it actually implement server processing and if not to try to figure out how we could add the feature... 

:)

Richard

[Vineet]

Yes.

As a side note, DABO is written fully in Python itself.
It is a full fledged framework using wxPython for GUI.
But I am using its database-interaction tier & business-logic tier
(dabo.biz & dabo.db).

Since we, web2py followers, are using "web2py" as a footing, you may
ignore the code regarding "ui" in the below-referred links.

http://thewinecellarbook.com/daboDocTestAlt/dabo.biz_module.html#dabo...
http://thewinecellarbook.com/daboDocTestAlt/dabo.db_module.html#dabo-db
http://thewinecellarbook.com/daboDocTestAlt/
http://www.dabodev.com

-- Vineet

On Feb 13, 8:16 pm, Richard Vézina <ml.richard.vez...@gmail.com>
wrote: