smarter sqlalchemy wtf dynamic filter in a flask app

Question

I have a form that filters a table, and i would like the form entry to build a sqlalchemy statement.

Because I have the option "No Filter", i can't just have the form field go into the filter('form field here'). But there must be a better way than doing every permutation like

AT= list(set([h.assigned for h in models.Request.query.all()]))
AT.append('No Filter')
form.assigned.choices=zip(AT,AT)
requestlist= models.Request.query.all() 
if form.validate_on_submit():

    if form.status.data=='No Filter' and form.assigned.data=='No Filter' and form.requestor.data=='No Filter':
            requestlist= models.Request.query.all() 
    elif form.status.data != 'No Filter' and form.assigned.data=='No Filter' and form.requestor.data=='No Filter':
        requestlist = db.session.query(models.Request).filter(models.Request.status.like(form.status.data)).all()
    elif form.status.data == 'No Filter' and form.assigned.data!='No Filter' and form.requestor.data=='No Filter':
        requestlist = db.session.query(models.Request).filter(models.Request.assigned.like(form.assigned.data)).all()
    elif form.status.data == 'No Filter' and form.assigned.data=='No Filter' and form.requestor.data!='No Filter':
        requestlist = db.session.query(models.Request).filter(models.Request.requestedBy.like(form.requestor.data)).all()
    elif form.status.data != 'No Filter' and form.assigned.data=='No Filter' and form.requestor.data!='No Filter':
        requestlist = db.session.query(models.Request).filter(models.Request.requestedBy.like(form.requestor.data))\
        .filter(models.Request.status.like(form.status.data)).all()
    elif form.status.data == 'No Filter' and form.assigned.data !='No Filter' and form.requestor.data!='No Filter':
        requestlist = db.session.query(models.Request).filter(models.Request.requestedBy.like(form.requestor.data))\
        .filter(models.Request.assigned.like(form.assigned.data)).all()
    elif form.status.data != 'No Filter' and form.assigned.data !='No Filter' and form.requestor.data =='No Filter':
        requestlist = db.session.query(models.Request).filter(models.Request.status.like(form.status.data))\
        .filter(models.Request.assigned.like(form.assigned.data)).all()
    elif form.status.data != 'No Filter' and form.assigned.data !='No Filter' and form.requestor.data !='No Filter':
        requestlist = db.session.query(models.Request).filter(models.Request.status.like(form.status.data))\
        .filter(models.Request.requestedBy.like(form.requestor.data))\
        .filter(models.Request.assigned.like(form.assigned.data)).all()

Answer 1

You can add multiple filter(...) clauses one after another:

if form.validate_on_submit():
    requestlist = models.Request.query

    if form.status.data != 'No Filter':
        requestlist = requestlist.filter(models.Request.status.like(form.status.data))

    if form.assigned.data != 'No Filter':
        requestlist = requestlist.filter(models.Request.assigned.like(form.assigned.data))

    if form.requestor.data != 'No Filter':
        requestlist = requestlist.filter(models.Request.requestedBy.like(form.requestor.data))

    return requestlist.all()

or more generically you could probably do the following:

field_names = ['status', 'assigned', 'requestor']
if form.validate_on_submit():
    requestlist = models.Request.query

    for field in field_names:
        f_value = getattr(form, field).data
        if f_value != 'No Filter':
            requestlist = requestlist.filter(getattr(models.Request, field).like(f_value))

    return requestlist.all()

The only problem i see is that sometimes names on the form and the model do not match. An example is requestedBy and requestor. In this case you might define a dictionary instead of a list in field_names.