> -----Original Message----- > From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] > On Behalf Of frankentux > Sent: 14 April 2011 14:42 > To: sqlalchemy > Subject: [sqlalchemy] Create a one-to-many relationship using > association object with two foreign key primary keys > > I have packages and repos. A package can be in many different repos > and a repo has many packages. I want to have an additional > relationship to capture the 'status' of a particular package in a > particular repo. This would be a many-to-many relationship with an > additional field, so I guess I have to use an Association object, as > described by the docs. > > When I create a 'normal' association object, it works fine. However, > as a next step I would like to add any number of comments to the > association object - i.e. in my case (below), I would like a PackRepo > object to have any number of comments - as a classic one-to-many. > > However, given that PackRepo itself has no 'id' but rather uses the > foreign key relationships to package.id and repo.id as primary keys, > I > don't know how to create the relationship to the package_repo table > when I'm building the comments_table - I can't simply say > packagerepo.id because packagerepo doesn't _have_ an id - it has two > foreign key primary keys as described above. > > Any ideas of what to do? > > package_table = Table('package',metadata, > Column('id',Integer,primary_key=True), > Column('name',String)) > > repo_table = Table('repo',metadata, > Column('id',Integer,primary_key=True), > Column('name',String)) > > comment_table = Table('comment',metadata, > Column('id',Integer,primary_key=True), > ### PROBLEM - HOW TO CREATE RELATIONSHIP TO package_repo ### > # Column('packagerepo_id', Integer, ForeignKey(### how to declare > this ###)), > Column('msg',String)) >
You just need to add a column to your comment_table for each key column in the target table. Something like this: comment_table = Table('comment',metadata, Column('id',Integer,primary_key=True), Column('package_id', Integer, ForeignKey('package_repo.package_id'), Column('repo_id', Integer, ForeignKey('package_repo.repo_id'), Column('msg',String)) I *think* SA will automatically work out the relationship condition based on those two foreign keys. Hope that helps, Simon > package_repo_table = Table('package_repo', metadata, > > Column('package_id',Integer,ForeignKey('package.id'),primary_key=True > ), > Column('repo_id',Integer,ForeignKey('repo.id'), primary_key=True), > Column('status',String,default='builds')) > > mapper(Package, package_table, properties={ > 'repos':relationship(PackRepo) > }) > > mapper(PackRepo, pack_repo_table, properties={ > 'repo':relationship(Repo), > 'comments': relationship(Comment) > }) > > mapper(Comment,comment_table) > > mapper(Repo, repo_table) > -- 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.