Create a one-to-many relationship using association object with two foreign key primary keys

[frankentux]

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

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)

[King Simon-NFHD78]

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

[GHZ]

Looks like you need to specify a composite ForeignKey

http://www.sqlalchemy.org/docs/core/schema.html?highlight=foreign_keys#sqlalchemy.schema.ForeignKeyConstraint

comment_table = Table('comment',metadata,
Column('id',Integer,primary_key=True),

Column('package_id', Integer),
Column('repo_id', Integer),
Column('msg',String),
ForeignKeyConstraint(['package_id', 'repo_id'],
['package_repo_table.package_id', 'package_repo_table.repo_id'])
)

[frankentux]

Thanks for the tips. It works with the ForeignKeyConstraint example in
the last comment

On Apr 14, 4:15 pm, GHZ <geraint.willi...@gmail.com> wrote:
> Looks like you need to specify a composite ForeignKey
>

> http://www.sqlalchemy.org/docs/core/schema.html?highlight=foreign_key...