thanks for the (insanely fast) help! wanted to avoid doing something unnecessarily odd.
i ended up wrapping the relation in an object proxy when passing it to the join. the proxy ANDs additional criterion into the primary or secondary join attributes of the relation based on what is supplied when creating the proxy. though clearly odd it seem to generally work. i'm not sure if this may break orm magic somewhere down the line... code looks like this: query(User).outerjoin((Email, RelationProxy(User.emails, Email.name != "bogus"))) the object proxy code is based on the following if anyone's interested: http://code.activestate.com/recipes/496741/ http://code.activestate.com/recipes/519639/ On Sep 14, 2:13 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote: > me wrote: > > > For certain orm queries with a 1-to-many relation i want to left outer > > join and then update the "on-clause" for that relation. Since the > > criteria changes between queries I cannot fix the join criteria when > > specifying my object/table mappings. > > > For example: > > tables: user, email > > relation: user.emails (1-many) > > > select * > > from user > > left outer join email on email.user_id = user.id and email.name > > like '%hello%' > > > While this is easy to write as a one off query in my case I need to be > > able to add variable filtering to the join on-clause and in a way that > > hopefully works for more complex relations. > > > So e.g. if I have a query built like this: > > query(user).outerjoin((email, emails)) > > > Is there a general way to add to the primary/secondary join criteria > > that is pulled from this emails relation? Or is there a better way to > > express this in SA that I've missed? > > the contract of query.outerjoin(SomeClass.property) is that you're doing a > plain join from A to B along pre-established routes. If you'd like the > criterion of the ON clause to be customized, the standard route is to > spell out the entire thing you want completely. The only potential time > saver here would be if you held onto the primaryjoin aspect of the > relation and used it in an AND clause, which at first looks like: > > query(User).outerjoin((Email, and_(email_primary_join, <other criterion>))) > > the next level would be that you'd pull "email_primary_join" from the > mapping. You can get at this via User.emails.property.primaryjoin. > > at the moment that's as automated as it gets as far as what's built in. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
