On Apr 21, 2011, at 8:55 AM, Ed Singleton wrote: > A colleague of mine submitted a question to StackOverflow ( > http://stackoverflow.com/questions/5666732/sqlalchemy-self-referential-relationship-not-including-self > ) which I've been struggling to answer (code given below for those who don't > want to visit the link). > > Given, for example, a Film table and a Countries table, how can you add a > relationship to the Film mapper that will give you a list of countries that > have the same film, but which doesn't include the Film itself?
The query above is probably not a valid candidate to be a relationship() - use a simple descriptor with Query for that. > > I tried: > > primaryjoin=sqlalchemy.and_( > film_table.c.country_id==film_table.c.country_id, > film_table.c.id!=film_table.c.id) > > but this doesn't work as it doesn't replace one of the `film_table.c.id`s > with a bindparam. > More generally, in a relationship, how do you force a column reference to be > to 'self' and how does SQLalchemy decide which references are to 'self' and > which are to the objects it's going to get? relationship() when used in a lazy load can inject bind parameters into the columns that are present on the "remote" side of the relationship, which can be equated to columns on the "local" side. In the case of a self-referential query the rules become more sensitive as it needs to know specifically which columns are considered "remote". Due to this design it's not capable of expressing a join of "table.id==table.id" where only one side is considered "remote". The short answer is that relationship() is designed to represent, both in class-level SQL generation as well as in instance-level attribute access, a simple reference to a scalar or collection based on traditional foreign key relationships, allowing a limited amount of leeway in how the join of the two tables is constructed. Because it has to work in lots of different contexts, the conditions it can handle are not open ended. -- 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.
