[sqlalchemy] Re: Confused by foreign_keys argument - Michael Bayer - com.googlegroups.sqlalchemy

4 messages in com.googlegroups.sqlalchemy[sqlalchemy] Re: Confused by foreign_...

From	Sent On	Attachments
King Simon-NFHD78	28 Mar 2007 07:39
Michael Bayer	28 Mar 2007 09:18
King Simon-NFHD78	28 Mar 2007 10:00	.py
Michael Bayer	28 Mar 2007 10:17

Subject:	[sqlalchemy] Re: Confused by foreign_keys argument
From:	Michael Bayer (mike...@zzzcomputing.com)
Date:	03/28/2007 09:18:28 AM
List:	com.googlegroups.sqlalchemy

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, ) } )