> -----Original Message-----
> From: [email protected] [mailto:[email protected]]
> 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 [email protected].
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.
