Datatables Query String Parsing

[villas]

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.

[Richard Vézina]

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

--
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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Richard Vézina]

Here they talk about data structure and list seems possible : https://datatables.net/manual/server-side#Example-data

Richard

[José Luis Redrejo]

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.

[villas]

Thanks for responses and José that code will be a good help.

In my case, parsing the query is quite trivial to extract a search string and the sort order,  which is all I needed just now.

After playing around more,  it seems to me that Web2py would be really improved with something like Datatables (as well as the sqlform grid).

• For small datasets you can feed in all the data at once and use js pagination and searching etc.  It's quick and avoids a lot of queries.
• For large datasets,  the ajax interaction is great.

The number of queries that the sqlform grid produces is very inefficient,  and that is why I have been experimenting with Datatables in the first place.

Indeed Datatables seems to fit really well with Massimo's ideas for the future (I mean making better use of the client-side and reducing bandwidth). 

Best regards.

[Richard Vézina]

In my case I didn't activate server side processing, I instead reduce the length of the dataset to be displayed by year slicing... I had buttons to specify which years of data the user wants and an "All" button with a notice it could be long to load... It has reduce the load time of the page... 

Someone can also, improve speed by not using pydal and instead make a raw sql query bypassing all the pyDAL parsing of records and dictionnary creation (db.table.fieldname) which are costy... You then rely on python slicing to get you data column by colomn...

[villas]

Hi Richard / everyone

Those are good strategies.  However,  I also feel that it is a pity that we are having to 'roll our own' solutions because the sqlform.grid just seems too slow.

Your idea of avoiding pyDAL seems to greatly reduce queries,  and so I am also tempted down this road.  However,  again,  this undermines the whole point of the framework. 

We need a more performant grid as part of the framework and my observations are these:
1. We need two options for loading data:  all at once,  and server-side via ajax. 
2. We need to fill the grid with complex queries,  including proper 'left join on' syntax,  and in-line selects.  We can then suppress all those magical data 'represents' queries which are all done with additional row-by-row sub-selects.  On one page of my sqlform.grid views it generated almost a hundred queries.  I was able to replace that with a single complex query. 
3. Ideally,  we should avoid reloading the whole page when we want to edit the data.  For example,  using modal forms.

Maybe I'm asking far too much.  However,  almost every app I write uses a data grid and associated create/edit forms.  In simple situations I am very grateful for the sqlform.grid.  In many other cases though,  I find myself writing similar code over and over to produce a more performant listing.  With my ideal framework,  I cannot help but feel that I should have a better starting point.

I know that I should try and bring solutions to this group rather than frustrations.  My suggestion is therefore: let's leverage something like Datatables!
If you got this far,  thanks for listening!

Best regards.

[Richard Vézina]

Yes Villas you get good points...

I think Massimo's had talked about abandonning SQLFROM.grid. I never been able to fully levrage it. Most of the time, I prefer handle forms myself, as they most of the time more complexe the what .grid() can handle. So there is no gain to having them to me... Tough I think we should look at SQLFORM.grid as a prototyping tools as CRUD was more then an end solution for crafting our apps. You build a prototype app fast then you rewrite it and use proper customized tools for the real final product... But it far from ideal... But I am not sure any frameworks can meet expectations of the broad wild audience... Some will argue that they don't want you to specify the grid they are going to use, other want it built in... For me, I would defenitly avoid using DTs as I found those kind of fancy grid too heavy (to many plugins, to many js, flash stuff, etc.)... I would prefer bare html/css closer solution, like build html table populate it and drop css from boostrap or other CSS lib. In this regard SQLFORM.grid is almost perfect, but I think it should be rewrite entirely with proper requirements and to me it should be strip to it main purpose GRID... Like this you don't have to manage complexe redirection with args and vars, etc. It should accept complexe query, so you are not limited to use it over as single table, CRUD select was good in this respect.

In the future w3p

I guess in the future web3py will not offer anything like SQLFORM.grid, just me thinking, base on what Massimo's has mention so far about web3py...

I think grid stuff will have to be handle more like this :

https://vuejs.org/v2/examples/grid-component.html

https://www.ag-grid.com/angular-getting-started/#gsc.tab=0

[villas]

Hi Richard,

Thanks for your points of view and I find the discussion interesting.  However I'm not sure that we are on the same page:

For me, I would defenitly avoid using DTs as I found those kind of fancy grid too heavy (to many plugins, to many js, flash stuff, etc.)

Datatables is not that heavy and,  anyhow,  that isn't the problem because it performs well when loaded,  which sqlform.grid does not.

I would prefer bare html/css closer solution, like build html table populate it and drop css from boostrap or other CSS lib. In this regard SQLFORM.grid is almost perfect

My point is that sqlform.grid is often totally inadequate for my needs: it doesn't paginate loaded datasets, cannot populate via ajax,  and makes far too many queries.  All of which means I have to make my own solutions (which I am not very good at). 

Don't get me wrong,  I am very grateful for what web2py does for me,  but I am just talking specifically about the grid here.

I was interested in the vue.js,  but I'm not entirely sure how it solves my problem of producing an efficient data grid listing. 

Best wishes.

 

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.

[Massimo Di Pierro]

I also think the grid is obsolete and its code is ugly. The concept of pagination is old. I think it is much better to have an api and a [more] button that adds item at the bottom, until a new search is done. I will post a vue.js example of that as soon as I can.

[villas]

Thanks Massimo and I will be very interested to try a Vue.js example.

In the meantime,  I have experimented with Datatables some more.  I wrote my own sql and used executesql and then fed the entire resultset all at once into Datatables.  My rows are in the hundreds rather than thousands,  but the usability of the app was totally transformed.  Once loaded,  which is also very fast,  the table responds instantly and searches and sorting are brilliant.  I just use a SQLFORM to add/edit records.  My staff almost cheered when they saw the new version,  and that underlines how frustrated they must have been with the old one.

I know this may sound too critical of the grid,  as some obviously think it only exists for prototyping.  However,  we also need this kind of thing to work properly in production too.  IMO if a good usable method isn't available in web2py,  we should at least have an 'official' recipe using another recommended component.

Best regards.

[Val K]

Hi, I did something  similar that what you are looking for  
jq_param2storage_w2p.py

usage in controller:

paresd_vars = jq_param_w2p(request.body)