Quick and dirty search form example

Considering models/db.py

status_options = {"0": "pending", "1": "confirmed", "3": "canceled"}

db.define_table("orders",
    Field("id_buyer", "reference auth_user"),
    Field("order_date", "date"),
    Field("status",
          requires=IS_IN_SET(status_options),
          represent= lambda value, row: status_options[value]
          ),
    Field("obs","text")
)

And the search function controllers/default.py

import datetime

@auth.requires_login()
def index():
    # default values to keep the form when submitted
    # if you do not want defaults set all below to None

    status_default = request.vars.status
    date_initial_default = \
        datetime.datetime.strptime(request.vars.date_initial, "%Y-%m-%d") \
            if request.vars.date_inicial else None
    date_final_default = \
        datetime.datetime.strptime(request.vars.date_final, "%Y-%m-%d") \
            if request.vars.date_final else None
    obs_default = request.vars.obs


    # The search form created with .factory
    form = SQLFORM.factory(
                  Field("status",
                        default=status_default
                        requires=IS_EMPTY_OR(
                            IS_IN_SET(status_options, zero="-- All --")
                        ),
                  ),
                  Field("date_initial", "date", default=date_initial_default),
                  Field("date_final", "date", default=date_final_default),
                  Field("obs", default=obs_default),
                  formstyle='divs',
                  submit_button="Search",
        
                  )

    # The base query to fetch all orders of the current logged user
    query = db.orders.id_buyer == auth.user_id                  

    # testing if the form was accepted              
    if form.process().accepted:
        # gathering form submitted values
        status = form.vars.status
        date_initial = form.vars.date_initial
        date_final = form.vars.date_final
        obs = form.vars.obs

        # more dynamic conditions in to query
        if status:
            query &= db.orders.status == status
        if date_initial:
            query &= db.orders.order_date >= date_initial
        if date_final:
            query &= db.orders.order_date <= date_final
        if obs:
            # A simple text search with %like%
            query &= db.orders.obs.like("%%%s%%" % obs)            

    count = db(query).count()
    results = db(query).select(orderby=~db.orders.data)
    msg = T("%s registers" % count )
    return dict(form=form, msg=msg, results=results)

Optionally you can create a view file in views/default/index.html

{{extend 'layout.html'}}
{{=form}}
<hr />
{{=msg}}
{{=results}}

the end result

Download the app: http://dl.dropbox.com/u/830444/web2py.app.busca.w2p

If you need a better and complex search engine I recommend Whoosh.