[web2py] parenthesis in SQLFORM.grid search

[Richard]

Hello,

It seems that SQLFORM.grid search tool not accept parenthesis?

Ex.:

Not possible 

(table.input_comment = "" or table.input_comment != None) and ...

Richard

[Niphlod]

is it something that requires an answer ? grid search is meant to be flexible AND user-friendly: you can come up with your own syntax and code your own search widget if you need something else....

[Richard Vézina]

I was looking for know if it was not implemented because it was really more complex or for some other reasons...

I found myself needing to pass queries trough url and I need to pass complexe "where clauses" which implies need for () sourrounding some queries groups...

For now I have this :

from gluon.dal import smart_query

keywords = \

    'table1.f1 = True and ' \

    'table1.f2 != {0}'.format(

        db(db.ref_table.f1 =='Annulé - Void'

           ).select(db.ref_table.id).first().id)

fields = [db.table1.f1,

          db.table1.f2]

active_set = db(smart_query(fields, keywords))

So I can DRYly write db_set and then pass the queries through URL like so :

URL(c='mycontroller', f='myfunc', vars=dict(keywords=keywords))

But, for more complexe "where clause" I am stuck...

I guess passing a web2py query directly into URL vars raise security issue that why I follow the path of SQLFORM.grid search tool for passing queries in vars...

QUESTION

Is smart_query() able to parse parenthesis queries some how?

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

[Richard Vézina]

smart_query only usage seems to be processing SQLFORM.grid search queries??

It's used in pyDAL too, but can't figure out yet what it used for.

smart_query() is not documented, I can write a little docstring for it, at least specifying it main purpose??

Richard

[Johann Spies]

Will it not be easier to encapsulate your complex query in a database view and run the grid on the view?

e.g.  in the controller:

db.execute(create view xx as complexquery)

db.define_table('xx',

                        Field1(),

                        Field2...

Johann

--

Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

[Richard Vézina]

It could work of course, but it hurts to have to do that to just filter out correctly a dropdown menu... What I was doing until now was to have a single returning json_feed which for each dropdown required... But it not try, I need to create almost identical function for each particular dropdown to feed typeahead widget with remote data... I am padding smart_query() with unittest right now before give a try to make it accept "grouped" query...

Richard

--

[Richard Vézina]

Recursive problem... I get closer, but have a lot of difficulty figuring out how to assembling the nested queries in the right order and the right depth of each query...

I have been so far able to extract a flatten dict of each individually nested query and translated them into web2py query...

But as explained above now I bang my head on my desk...

:)

Richard

[Richard Vézina]

Here what I got for now :

from pyparsing import nestedExpr

# Not the worse case but has enough complexity to start with
grouped_query_string = \
        '((((a_table.integer_field > 1) & ' \
        '   (a_table.integer_field not equal 2) &' \
        '   (~a_table.integer_field in "200, 300, 400")) & ' \
        '(a_table.integer_field =< 3000)) | ' \
        '(a_table.boolean_field = "True"))'

list_of_nested_query_string = nestedExpr('(', ')').parseString(grouped_query_string).asList()

ops_translation = {
        '==': '=',
        '!=': '!=',
        '<': '<',
        '>': '>',
        '<=': '<=',
        '>=': '>=',
        '<>': '!=',
        '=<': '<=',
        '=>': '>=',
        '=': '=',
        'less or equal than': '<=',
        'greater or equal than': '>=',
        'equal or less than': '<=',
        'equal or greater than': '>=',
        'less or equal': '<=',
        'greater or equal': '>=',
        'equal or less': '<=',
        'equal or greater': '>=',
        'not equal to': '!=',
        'not equal': '!=',
        'equal to': '=',
        'equal': '=',
        'equals': '=',
        'less than': '<',
        'greater than': '>',
        'starts with': 'startswith',  # ??
        'ends with': 'endswith',  # ??
        'not in': 'notbelongs',  # ??
        'in': 'belongs',  # ??
        'is': '='
    }

# Base traverse() function which I derive the rest
# def traverse(o, tree_types=(list, tuple)):
#     if isinstance(o, tree_types):
#         for value in o:
#             for subvalue in traverse(value):
#                 yield subvalue
#     else:
#         yield o

# Here the beast
queries = {}

nested = []

def traverse(o, tree_types=(list, tuple)):
    ops = ('&', '|')
    q = None
    negative = None
    if isinstance(o, tree_types):
        # nested.append('()')
        # if len(o) > 2 and o[1] in ops:
        #     nested.append(o[1])
        if len(o) > 2 and o[1] not in ops:
            print o
            table, field_name = o[0].split('.')
            if table[0] == '~':
                negative = True
                table = table[1:]
            field = db[table][field_name]
            op = ops_translation[' '.join(o[1:-1])]
            value = o[-1]
            if op == '=':
                q = field == value
            elif op == '<':
                q = field < value
            elif op == '>':
                q = field > value
            elif op == '<=':
                q = field <= value
            elif op == '>=':
                q = field >= value
            elif op == '!=':
                q = field != value
            elif op == 'belongs':
                q = field.belongs(value[1:-1].split(','))
            elif op == 'notbelongs':
                q = ~field.belongs(value[1:-1].split(','))
            elif field.type in ('text', 'string', 'json'):
                if op == 'contains':
                    q = field.contains(value)
                elif op == 'like':
                    q = field.ilike(value)
                elif op == 'startswith':
                    q = field.startswith(value)
                elif op == 'endswith':
                    q = field.endswith(value)
                else:
                    raise RuntimeError("Invalid operation")
            queries[tuple(o)] = q
            print q
        for value in o:
            for subvalue in traverse(value):
                yield subvalue
    else:
        yield o

# For invoquing the generator
for e in traverse(list_of_nested_query_string): e  # Notting come out except print

# Then have a look to queries

I build a dict of tuple query has key to try to replace the sublist in the "list_of_nested_query_string", but then I need to traverse it again and build nested query in another traverse derived function... Not clean code...

# Here model definition for testing purpose
db.define_table('a_table',
                Field('string_field', 'string'),
                Field('text_field', 'text'),
                Field('boolean_field', 'boolean'),
                Field('integer_field', 'integer'),
                Field('double_field', 'double'),
                # Field('decimal_field', 'decimal'),
                # Field('date_field', 'date'),
                # Field('time_field', 'time'),
                # Field('datetime_field', 'datetime'),
                # Field('reference_field', 'reference referred_table'),
                # Field('list_string_field', 'list:string'),
                # Field('list_integer_field', 'list:integer'),
                # Field('list_reference_field', 'list:reference referred_table')
                )

fields = [db.a_table.string_field,
          db.a_table.text_field,
          db.a_table.boolean_field,
          db.a_table.integer_field,
          db.a_table.double_field,
          # db.a_table.decimal_field,
          # db.a_table.date_field,
          # db.a_table.time_field,
          # db.a_table.reference_field,
          # db.a_table.list_string_field,
          # db.a_table.list_integer_field,
          # db.a_table.list_reference_field
          ]