> -----Original Message-----
> From: [email protected] [mailto:[email protected]]
> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
