I have used it several times, I thought of doing a reusable class for it,
but did't have time to do it yet.
For a query with only one table involved this is one code example.
I use the same code, with a small modifications in the way field values are
requested, for queries with joined tables. That's why there are some
variables here that might not make sense when only one table is used
(table_order, table_search, etc.)
It includes searching for several field types:
For this example the table used is called api_logs:
In the view
var table = $('#lstable').DataTable({
sPaginationType: "full_numbers",
order: [[0, "desc"]],
processing: true,
serverSide: true,
autoWidth: true,
searching: true,
ajax: {
"url":"{{ =URL('api','logs_table.json') }}",
"type": "POST"
},
........
In the controller:
@service.json
def 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
def field_names(index):
name = request.vars['columns[%s][data]' % index]
return ('', name)
def field_names_join(index):
name = request.vars['columns[%s][data]' % index]
table = name.split("_")[0]
field = "_".join(name.split('_')[1:])
return (table, field)
if 'order[0][column]' in request.vars:
order_column = request.vars['order[0][column]']
else:
order_column = '0'
_, field_order = field_names(order_column)
table_order = 'api_logs'
orderby = db[table_order][field_order]
if 'order[0][dir]' in request.vars:
if request.vars['order[0][dir]'] == 'desc':
orderby = ~orderby
filtered_vars = dict(filter(lambda (a, b): b != '' and
'[search][value]' in a, request.vars.items())).keys()
for var_name in filtered_vars:
col = var_name.replace('columns[', '').replace('][search][value]',
'')
_, field_search = field_names(col)
table_search = 'api_logs'
field_type = db[table_search][field_search].type
if field_type == 'datetime':
dates = request.vars[var_name].split(' - ')
if len(dates) > 1:
fmt = '%d/%m/%Y'
start_date = datetime.strptime(dates[0], fmt)
end_date = datetime.strptime(dates[1], fmt) +
timedelta(days=1)
new_filter = (db[table_search][field_search] >= start_date)
& (db[table_search][field_search] < end_date)
elif field_type == 'boolean':
new_filter = db[table_search][field_search] == ('tr' in
request.vars[var_name].lower() or
'ye' in request.vars[var_name].lower())
elif field_type in ('string', 'text'):
string_filter = request.vars[var_name]
new_filter = db[table_search][field_search].ilike('%s%%' %
string_filter)
else:
new_filter = None
if new_filter:
if query_filter:
query_filter = query_filter & new_filter
else:
query_filter = new_filter
if query_filter is None:
query_filter = db.api_logs.id > 0
query = db(query_filter).select(db.api_logs.ALL,
limitby=(iDisplayStart, iDisplayStart + iDisplayLength),
orderby=orderby)
iTotalRecords = db(query_filter).count()
aaData = []
for row in query:
datarow = {}
for col in row:
if row[col] is not None:
if col == 'api_logs' or '_record' in col:
# internal dal col names
continue
elif col == 'added': # datetime column , to be formatted
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)
I hope it helps
Regards
José L.
2017-07-28 14:59 GMT+02:00 Richard Vézina <ml.richard.vez...@gmail.com>:
> Here they talk about data structure and list seems possible :
> https://datatables.net/manual/server-side#Example-data
>
> Richard
>
> On Fri, Jul 28, 2017 at 8:52 AM, Richard Vézina <
> ml.richard.vez...@gmail.com> wrote:
>
>> Hello Villas,
>>
>> The subject interrest me, as I use DTs, but not the server-side
>> feature... But, I am not sure what you want to achieve... I gonna read a
>> bit about DTs server-side, waiting for precision from you and maybe will
>> understand better your question then...
>>
>> Richard
>>
>>
>>
>> On Thu, Jul 27, 2017 at 10:01 AM, villas <villa...@gmail.com> wrote:
>>
>>> Is there an simple way to parse this into an easier-to-work-with
>>> object. I mean for example 'columns' could be a list.
>>>
>>> {'columns[0][data]': 'first_name',
>>> 'columns[0][name]': '',
>>> 'columns[0][orderable]': 'true',
>>> 'columns[0][search][regex]': 'false',
>>> 'columns[0][search][value]': '',
>>> 'columns[0][searchable]': 'true',
>>> 'columns[1][data]': 'last_name',
>>> 'columns[1][name]': '',
>>> 'columns[1][orderable]': 'true',
>>> 'columns[1][search][regex]': 'false',
>>> 'columns[1][search][value]': '',
>>> 'columns[1][searchable]': 'true',
>>> 'draw': '1',
>>> 'length': '2',
>>> 'order[0][column]': '0',
>>> 'order[0][dir]': 'asc',
>>> 'search[regex]': 'false',
>>> 'search[value]': '',
>>> 'start': '0'}
>>>
>>> BTW this is from a Datatables post request for creating server-side sql
>>> queries. Maybe someone has already figured this out. I'm just trying to
>>> save time in creating a web2py query.
>>>
>>> Many thanks.
>>>
>>> --
>>> 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 web2py+unsubscr...@googlegroups.com.
>>> 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 web2py+unsubscr...@googlegroups.com.
> 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 web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
