That compiles and appears to run in the small test program attached, but if you look at the query generated when accessing the 'cs' property, it doesn't actually use the join condition: SELECT c.id AS c_id, c.name AS c_name FROM c, a_b, b_c WHERE ? = a_b.a_id AND b_c.c_id = c.id ORDER BY a_b.oid ie. the a_b.b_id = b_c.b_id clause is missing. If you aren't keen on the 'viewonly' pattern, how would you recommend doing this? Just by adding a normal python property and doing a query? The main reason I like setting it up as a relation is for the potential of making it eager-loading just by changing a single flag. Thanks, Simon
________________________________ From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Michael Bayer Sent: 28 March 2007 17:19 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: Confused by foreign_keys argument what it cant locate are foreign keys between the parent and child tables, "a" and "c"....because there arent any. when you have a many-to-many, the rules for figuring out the relationship change, and it knows to do that by the presence of the "secondary" argument. so if you can manufacture a "secondary" table you can do this: secondary = a_b_table.join(b_c_table, onclause=a_b_table.c.b_id==b_c_table.c.b_id) mapper( A, a_table, properties={'cs': relation(C, secondary=secondary, primaryjoin=a_table.c.id==secondary.c.a_b_a_id, secondaryjoin=secondary.c.b_c_c_id==c_table.c.id, viewonly=True, ) } ) im not totally sure the lazy clause is going to work but try it out. this goes back to my general dislike of "viewonly" and how i cant generally support it, becuase as the rules for relationships get more strict and accurate, cases like these become harder to model. On Mar 28, 2007, at 10:39 AM, King Simon-NFHD78 wrote: a_table = Table('a', metadata, Column('id', Integer, primary_key=True), Column('name', String(16)), ) b_table = Table('b', metadata, Column('id', Integer, primary_key=True), Column('name', String(16)), ) c_table = Table('c', metadata, Column('id', Integer, primary_key=True), Column('name', String(16)), ) a_b_table = Table('a_b', metadata, Column('a_id', Integer, ForeignKey('a.id'), primary_key=True), Column('b_id', Integer, ForeignKey('b.id'), primary_key=True), ) b_c_table = Table('b_c', metadata, Column('b_id', Integer, ForeignKey('b.id'), primary_key=True), Column('c_id', Integer, ForeignKey('c.id'), primary_key=True) ) class A(object): pass class B(object): pass class C(object): pass mapper(B, b_table) mapper(C, c_table) ############################################################# # How can I create a mapper on A with a property that gives # all the 'C' objects? ############################################################# # This doesn't work - it requires the foreign_keys parameter # to be passed, but I don't know what to pass. mapper( A, a_table, properties={'cs': relation(primaryjoin=and_(a_table.c.id == a_b_table.c.a_id, a_b_table.c.b_id == b_c_table.c.b_id, c_table.c.id == b_c_table.c.c_id), viewonly=True, ) } ) --~--~---------~--~----~------------~-------~--~----~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
join2.py
Description: join2.py
