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 -~----------~----~----~----~------~----~------~--~---
