web2py[:]


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

Dynamic search
Recipe by Nathan Freeze (mrfreeze) on 2010-04-26 in Tools

This creates a form that allows the user to dynamically search and select fields in a table

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

dynamic search form

Related slices

Comments (5)

  • Login to post



  • 0
    nipun-bhardwaj-10101 9 years ago

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


  • 0
    wasuaje 13 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 14 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 14 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 9 years ago

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


  • 0
    mrfreeze 14 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 9 years ago

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

Hosting graciously provided by:
Python Anywhere