On Fri, 6 May 2011 17:11:39 +0100 "King Simon-NFHD78" <simon.k...@motorolasolutions.com> wrote:
> > -----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 > THANKS!!! Works very fine :-)) -- ------------------------------------------------------------------- (o_ (o_ //\ Coltivate Linux che tanto Windows si pianta da solo. (/)_ V_/_ +------------------------------------------------------------------+ | ENRICO MORELLI | email: more...@cerm.unifi.it | | * * * * | phone: +39 055 4574269 | | University of Florence | fax : +39 055 4574253 | | CERM - via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY | +------------------------------------------------------------------+ -- 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.