Using sa.tuple_ looks like the nicest solution here, but back to your
original question about building "or" clauses, expressions built with or_
can be passed as arguments to or_ to build more complex expressions. One
simple iterative way to do this is:
clauses = or_() # empty starting value
for something in criteria:
clauses = or_(clauses, next_clause)
# equivalent to: clauses = clauses | next_clause
query = query.filter(clauses)
But that's just a long-winded way to express a reduce operation*, so for
your example you could also write:
import sqlalchemy as sa
criteria = (('male', 35), ('female', 35))
Useraccount = model.Useraccount
query = session.query(Useraccount)
ands = [sa.and_(Useraccount.gender == gender, Useraccount.age == age)
for
gender, age in criteria]
or_clauses = reduce(sa.or_, ands)
query = query.filter(or_clauses)
* Yes, I know in python 3 they recommend a for loop instead of reduce, but
using it here prevents you from having to create an empty starting value,
and this way you don't have to use an inner loop to mutate a value outside
the loop on each iteration. If you might be dealing with empty "ands" lists
then you'd need reduce(sa.or_, ands, sa.or_()), which does lose readability.
On Fri, Feb 22, 2013 at 11:58 AM, Conor <conor.edward.da...@gmail.com>wrote:
> On 02/21/2013 07:31 PM, Jonathan Vanasco wrote:
> > basd on a bunch of error messages, this example works...
> >
> > criteria = ( ('male',35),('female','35) )
> > query = session.query( model.Useraccount )
> > ands = []
> > for set_ in criteria :
> > ands.append(\
> > sqlalchemy.sql.expression.and_(\
> > model.Useraccoun.gender == set_[0] ,
> > model.Useraccoun.age == set_[1] ,
> > )
> > )
> > query = query.filter(\
> > sqlalchemy.sql.expression.or_( *ands )
> > )
> > results= query.all()
> >
> > this seems really awkward though. is there a better way to build up a
> > set of dynamic "or" criteria ?
> >
> For this specific case, if your database supports it, you can use the
> tuple_ construct:
>
> criteria = (('male', 35), ('female', 35))
> query = session.query(model.Useraccount)
> query = query.filter(sa.tuple_(model.Useraccount.gender,
> model.Useraccount.age).in_(criteria))
> results = query.all()
>
> It's cleaner and should give better index usage.
>
> -Conor
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
