> -----Original Message----- > From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] > On Behalf Of Enrico Morelli > Sent: 06 May 2011 16:20 > To: sqlalchemy > Subject: [sqlalchemy] Dynamic query > > Dear all, > > I've a form where people fill one or more fields to search in a db. > For the moment I solve it using a lot of if statement and a lot of > different query based on the filled fields. Something like that: > > if start_date and end_date and instrument and details and technician: > c.results = > > Session.query(Repairs).filter(and_(Repairs.start_date>=start_date, > Repairs.end_date<=end_date, > Repairs.instrument_id==instrument, > Repairs.details.like('%%%s%%' % details), > Repairs.technician.like('%%%s%%' % technician) > )).order_by('start_date').all() > > elif start_date and end_date and instrument and details: > c.results = > > Session.query(Repairs).filter(and_(Repairs.start_date>=start_date, > Repairs.end_date<=end_date, > Repairs.instrument_id==instrument, > Repairs.details.like('%%%s%%' % > details), )).order_by('start_date').all() > > and so on for each combination (for 5 fields I have 20 query!). There > is > a way to do that in a more dynamic way? >
You can call Query.filter multiple times. Here's an example: query = Session.query(Repairs) if start_date: query = query.filter(Repairs.start_date >= start_date) if end_date: query = query.filter(Repairs.end_date <= end_date) if instrument: query = query.filter(Repairs.instrument_id == instrument) # etc. results = query.order_by('start_date').all() Each filter condition will be combined using AND. Hope that helps, Simon -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.