Hi Jose,
Completely understandable on the code side of things and appreciate your
sharing. I will for sure revisit the Datatables option this week with the
information you have provided.
Once again thanks for your time and assistance
John
On Thursday, 16 May 2019 13:06:46 UTC+2, José L. wrote:
>
> 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]
> <javascript:>>) 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] <javascript:>.
>> 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/11ae79a6-121a-40cb-9285-b8071c49c6ed%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
