is there a more proper way to chain dynamic "or" clauses ?

[Jonathan Vanasco]

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 ?

[Simon King]

It doesn't look too awkward to me - what sort of API would you prefer?

Note that and_ and or_ are available in the top-level "sqlalchemy"
namespace. Also, backslashes aren't necessary on the end of lines when
you're inside brackets, so your example could actually look like this:

import sqlalchemy as sa

criteria = (('male', 35), ('female', 35))
Useraccount = model.Useraccount
query = session.query(Useraccount)
ands = []
for gender, age in criteria:
ands.append(
sa.and_(
Useraccount.gender == gender,
Useraccount.age == age,
)
)
query = query.filter(sa.or_(*ands))
results= query.all()

It's entirely subjective, but I find that easier to read.

You can also use "&" and "|" but I think you have to be a little
careful with operator precedence. You can probably write this:

ands.append((Useraccount.gender == gender) & (Useraccount.age == age))

Simon

[Jonathan Vanasco]

thanks.

i was really focused on the "query.filter(sa.or_(*ands))" concept.
that's what seemed kind of weird to me.

[Conor]

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

[Eric Rasmussen]

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.

--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@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.

[Michael Bayer]

On Feb 23, 2013, at 1:13 AM, Eric Rasmussen <ericra...@gmail.com> wrote:

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)

this is fine but I'd make one offhand note that nesting the conjunctions, i.e. or_(or_(or_(or_(x, y), z), q), b) has the effect of the compiler traversing it in a deep recursion loop, if you're using a lot of values (like, hundreds).  We had a user getting recursion overflows due to this.  I illustrated a compiler plug in for this user that did a non-recursive "unwrap" of the nested structure first before passing it down to the default compiler, which isn't built in because it adds a good chunk of performance overhead to all conjunctions.  Ultimately he went with doing or_(*everything) instead of his original approach of "x |= (y), x |= (q)" etc.

[Rob Crowell]

I'm not sure of the proper way to link to previous discussions (or indeed if I am even the hapless user that Michael was referring to!), but this sounds like what Michael was talking about for reference:

https://groups.google.com/d/topic/sqlalchemy/ETFlrQrmdHk/discussion