I can't show you all the code because the property belongs to the person
who asked me to do it,
This is the part that takes the dataables json requests, fetch the records
and format them to be understood by datatables. I am skipping the
sorting/filtering part , that builds the *query_filter* and *orderby*
variables
I have also changed the table name by MY_TABLE and remove one field name.
Also, beware in this case the total number of records uses count(), you
will have to replace it if your table has many records.
Hope it helps you.
José L.
@service.jsondef logs_table():
query_filter = None
if request.vars.start is not None:
iDisplayStart = int(request.vars.start)
else:
iDisplayStart = 0
if request.vars.length is not None:
iDisplayLength = int(request.vars.length)
else:
iDisplayLength = 10
if request.vars.draw is not None:
sEcho = int(request.vars.draw)
else:
sEcho = 1
....
....
query = db(query_filter).select(db.MY_TABLE.ALL,
limitby=(iDisplayStart, iDisplayStart + iDisplayLength),
orderby=orderby)
iTotalRecords = db(query_filter).count()
# iTotalRecords = 1000000
aaData = []
for row in query:
datarow = {}
for col in row:
if row[col] is not None:
if col == 'MY_TABLE' or '_record' in col:
# internal dal col names
continue
elif col == 'name of field that is a datetime':
datarow[col] = row[col].strftime('%Y-%m-%d %H:%M:%S')
else:
datarow[col] = row[col]
else:
datarow[col] = ''
aaData.append(datarow)
return dict(draw=sEcho, recordsTotal=iTotalRecords,
recordsFiltered=iTotalRecords, data=aaData)
El jue., 16 may. 2019 a las 12:51, John Bannister (<[email protected]>)
escribió:
> Hey Jose,
>
> Thanks for the quick response. Much appreciated. Do you happen to have a
> small working example of Datatables setup in server mode that I could take
> a look at to hopefully save some time? I last looked at it a few years ago
> as mentioned and any pointers would be great.
>
> Thanks in advance
> John
>
> On Thursday, 16 May 2019 12:42:17 UTC+2, José L. wrote:
>>
>> Hey John,
>>
>> I have used datatables with web2py in some projects and it was faster
>> than smartgrid when used in server mode.
>> Whenever I had performance problems was because the code used .count() to
>> show the total number of records. In my case the tables have several
>> millions of records and that was way too slow.
>> That's a common problem in many databases engines, not a datatables
>> problem.
>>
>> When using an aprox. number instead of executing the sql count() command,
>> the datatable works fine showing hundred of rows from a table.
>>
>> You have to make your own code for the server side, to support filter or
>> sort if you need them, but it's quite straightforward.
>>
>> José L.
>>
>> El jue., 16 may. 2019 a las 11:42, John Bannister (<[email protected]>)
>> escribió:
>>
>>> Hi All,
>>>
>>> I have a number of web2py applications that use the grid/smartgrid
>>> functionality pretty heavily. The majority of the applications are working
>>> with large data sets (some tables have > 20M records) and everything runs
>>> fine.
>>>
>>> With the coming of Web3Py (which looks great) my question is what would
>>> be a viable equivalent to web2py's grid/smartgrid?
>>>
>>> I looked at Datatables some time back which worked great on smaller
>>> datasets but anything over 50K (on my testbed) starts to become unusable. I
>>> also implemented some server side processing for Datatables but never quite
>>> managed to get a usable solution or at least nothing that could handle big
>>> data sets as efficiently as web2py.
>>>
>>> So basically looking for something that can handle large datasets that
>>> will plug into web3py if anyone has any recommendations they would be much
>>> appreciated or if anyone has managed to get Datatables working well with
>>> large data sets some guidance would be great.
>>>
>>> Thanks in advance
>>>
>>> --
>>> 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 the Google
>>> Groups "web2py-users" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to [email protected].
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/web2py/8e109e58-f4ac-4272-a835-d86e5101c7fe%40googlegroups.com
>>> <https://groups.google.com/d/msgid/web2py/8e109e58-f4ac-4272-a835-d86e5101c7fe%40googlegroups.com?utm_medium=email&utm_source=footer>
>>> .
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>> --
> 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 the Google Groups
> "web2py-users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/web2py/49640192-d789-44e5-a4e9-db852e8d760f%40googlegroups.com
> <https://groups.google.com/d/msgid/web2py/49640192-d789-44e5-a4e9-db852e8d760f%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
> For more options, visit https://groups.google.com/d/optout.
>
--
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 the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/web2py/CAHpm-q2VQgrFm4oQRoUT%2BckP9juFB%2BLmoeSJTedB1A%3Dabjq-gw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
