CODEWITHSUNDEEP
pythondatabasesqlalchemy
Monfico
Monfico

Reputation: 154

One to many + one relationship in SQLAlchemy?

I'm trying to model the following situation: A program has many versions, and one of the versions is the current one (not necessarily the latest).

This is how I'm doing it now:

class Program(Base):
    __tablename__ = 'programs'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    current_version_id = Column(Integer, ForeignKey('program_versions.id'))

    current_version = relationship('ProgramVersion', foreign_keys=[current_version_id])
    versions = relationship('ProgramVersion', order_by='ProgramVersion.id', back_populates='program')


class ProgramVersion(Base):
    __tablename__ = 'program_versions'
    id = Column(Integer, primary_key=True)
    program_id = Column(Integer, ForeignKey('programs.id'))
    timestamp = Column(DateTime, default=datetime.datetime.utcnow)

    program = relationship('Filter', foreign_keys=[program_id], back_populates='versions')

But then I get the error: Could not determine join condition between parent/child tables on relationship Program.versions - there are multiple foreign key paths linking the tables. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

But what foreign key should I provide for the 'Program.versions' relationship? Is there a better way to model this situation?

Upvotes: 3

Views: 1055

Answers (2)

VergeA
VergeA

Reputation: 89

This design is not ideal; by having two tables refer to one another, you cannot effectively insert into either table, because the foreign key required in the other will not exist. One possible solution in outlined in the selected answer of this question related to microsoft sqlserver, but I will summarize/elaborate on it here.

A better way to model this might be to introduce a third table, VersionHistory, and eliminate your foreign key constraints on the other two tables. 

class VersionHistory(Base):
    __tablename__ = 'version_history'
    program_id = Column(Integer, ForeignKey('programs.id'), primary_key=True)
    version_id = Column(Integer, ForeignKey('program_version.id'), primary_key=True)
    current = Column(Boolean, default=False)
    # I'm not too familiar with SQLAlchemy, but I suspect that relationship 
    # information goes here somewhere

This eliminates the circular relationship you have created in your current implementation. You could then query this table by program, and receive all existing versions for the program, etc. Because of the composite primary key in this table, you could access any specific program/version combination. The addition of the current field to this table takes the burden of tracking currency off of the other two tables, although maintaining a single current version per program could require some trigger gymnastics.

HTH!

Upvotes: 0

univerio
univerio

Reputation: 20548

Circular dependency like that is a perfectly valid solution to this problem.

To fix your foreign keys problem, you need to explicitly provide the foreign_keys argument.

class Program(Base):
    ...
    current_version = relationship('ProgramVersion', foreign_keys=current_version_id, ...)
    versions = relationship('ProgramVersion', foreign_keys="ProgramVersion.program_id", ...)

class ProgramVersion(Base):
    ...
    program = relationship('Filter', foreign_keys=program_id, ...)

You'll find that when you do a create_all(), SQLAlchemy has trouble creating the tables because each table has a foreign key that depends on a column in the other. SQLAlchemy provides a way to break this circular dependency by using an ALTER statement for one of the tables:

class Program(Base):
    ...
    current_version_id = Column(Integer, ForeignKey('program_versions.id', use_alter=True, name="fk_program_current_version_id"))
   ...

Finally, you'll find that when you add a complete object graph to the session, SQLAlchemy has trouble issuing INSERT statements because each row has a value that depends on the yet-unknown primary key of the other. SQLAlchemy provides a way to break this circular dependency by issuing an UPDATE for one of the columns:

class Program(Base):
    ...
    current_version = relationship('ProgramVersion', foreign_keys=current_version_id, post_update=True, ...)
    ...

Upvotes: 3

Related Questions