If you benefit from web2py hope you feel encouraged to pay it forward by contributing back to society in whatever form you choose!

Setting up

Place this code in a model

def build_query(field, op, value):
    if op == 'equals':
        return field == value
    elif op == 'not equal':
        return field != value
    elif op == 'greater than':
        return field > value
    elif op == 'less than':
        return field < value
    elif op == 'starts with':
        return field.like(value+'%')
    elif op == 'ends with':
        return field.like('%'+value)
    elif op == 'contains':
        return field.like('%'+value+'%')

def dynamic_search(table):
    tbl = TABLE()
    selected = []
    ops = ['equals','not equal','greater than','less than',
           'starts with','ends with','contains']
    query = table.id > 0    
    for field in table.fields:
        chkval = request.vars.get('chk'+field,None)
        txtval = request.vars.get('txt'+field,None)
        opval = request.vars.get('op'+field,None)
        row = TR(TD(INPUT(_type="checkbox",_name="chk"+field,
                          value=chkval=='on')),
                 TD(field),TD(SELECT(ops,_name="op"+field,
                                     value=opval)),
                 TD(INPUT(_type="text",_name="txt"+field,
                          _value=txtval)))
        tbl.append(row)
        if chkval:
            if txtval:
                query &= build_query(table[field], 
                                opval,txtval)
            selected.append(table[field])           
    form = FORM(tbl,INPUT(_type="submit"))
    results = db(query).select(*selected)
    return form, results

Try it out

The dynamic_search function returns both the form and the results.

def index():
    form,results = dynamic_search(db.things)
    return dict(form=form,results=results)

alt text

Related slices

Comments (5)

  • Login to post



  • 0
    nipun-bhardwaj-10101 5 years ago

    Can you send me the full code of dynamic search with models and views.


  • 0
    wasuaje 8 years ago
    Hello there i had to make some changes: improve performance for big tables always return de id of table even if it is not selected previously add another param (string list) to accept your own list of fields to search for. Result returns None if you entered to the form the first time, so you can "capture" this case and decide even show or not the result later in your view Use it like this
    
    def search:
    frmsearch,results = dynamic_search(db[tabla],'descripcion, fecha_solicitud')
    
    if results <> None: 		
    	return dict(tabla=tabla, frmsearch=frmsearch,  lista=results)			
    else: 
    	return dict(tabla=tabla, frmsearch=frmsearch)
    
    
    The code
    
    def dynamic_search(table,strfields):
    	tbl = TABLE()
    	selected = []
    	ops = ['igual','no igual a','mayor que','menor que','comienza con','termina en','contiene']
    	#print table.fields
    	query = table.id > 0    
    	for field in table.fields:
    		if field in strfields:
    			chkval = request.vars.get('chk'+field,None)
    			txtval = request.vars.get('txt'+field,None)
    			opval = request.vars.get('op'+field,None)
    			row = TR(TD(INPUT(_type="checkbox",_name="chk"+field,value=chkval=='on')), \
    		             TD(field),TD(SELECT(ops,_name="op"+field,value=opval)), \
    		             TD(INPUT(_type="text",_name="txt"+field, _value=txtval)))
    			tbl.append(row)
    			if chkval:
    				if txtval:					
    					query &= build_query(table[field], opval,txtval)
    					selected.append(table[field]) 
    	
    	if len(selected) > 0:
    			selected.append(table.id) 
    			results = db(query).select(*selected)
    	else:
    		results = None
    	
    	form = FORM(tbl,INPUT(_type="submit"))
    	return form, results  
    
    

  • 0
    mrfreeze 10 years ago
    This has been put in web2py tools.py trunk as Crud.search. Please create patches for any features you would like added.

  • 0
    cfhowes 10 years ago
    I'd like to add some things to dynamic_search(): limitby - improves performance on large tables, and orderby so that i can sort. Also, i added id and class to the input so i get the date-picker widget automagically for dates. There were a couple of changes i made since i'm using it on Google App Engine as well.
    def dynamic_search(table):
        """
        Build the input for for selection criteria, and runs a query
        based on the current criteria
        """
        tbl = TABLE()
        selected = []
        ops = ['equals','not equal','greater than','less than',
               'starts with','ends with','contains']
        if request.env.web2py_runtime_gae:
            ops = ['equals','not equal','greater than','less than']
    
        query = table.id > 0    
        for field in table.fields:
            chkval = request.vars.get('chk'+field,None)
            txtval = request.vars.get('txt'+field,None)
            opval = request.vars.get('op'+field,None)
            row = TR(TD(INPUT(_type="checkbox",_name="chk"+field,
                              value=chkval=='on')),
                     TD(field),TD(SELECT(ops,_name="op"+field,
                                         value=opval)),
                     TD(INPUT(_type="text",_name="txt"+field,_id="txt"+field,
                              _value=txtval, _class=table[field].type)))
            tbl.append(row)
            if chkval:
                if txtval:
                    query &= build_query(table[field], 
                                    opval,txtval)
                selected.append(table[field])           
    
        #limit the results
        row = TR(TD(), TD('Limit to'), TD(),
                 TD(INPUT(_type="text",_name="limitby", _id="limitby",
                          _value=request.vars.get('limitby', "100"),
                          _class="string")))
        tbl.append(row)
    
        #order the requelts
        opts = table.fields[:]
        opts.remove("id")
        opts = [""] + opts
        row = TR(TD(), TD('Order by'), TD(),
                 TD(SELECT(opts,_name="orderby",
                           value=request.vars.get('orderby', None))))
        tbl.append(row)
    
    
        form = FORM(tbl,INPUT(_type="submit"))
    
    
        #for GAE the orderby must already be a table field, not a string, so convert
        orderby=None
        if request.vars.orderby:
            field = request.vars.orderby
            orderby=db[table][field]
    
        results=db(query).select(limitby=(0,int(request.vars.get('limitby','100'))), 
                                            orderby=orderby, *selected)
    
        return form, results
    
    replies (1)
    • nipun-bhardwaj-10101 5 years ago

      Can you send me the full code of dynamic search with models and views.


  • 0
    mrfreeze 10 years ago
    Thanks. I'm working on a version for gluon/tools.py and will try to incorporate your changes.
    replies (1)
    • nipun-bhardwaj-10101 5 years ago

      Can you send me the full code of dynamic search with models and views.


Hosting graciously provided by:
Python Anywhere