CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
3kt
3kt

Reputation: 2553

SQLAlchemy: foreign key to multiple tables

Let's consider 3 tables:

Each book has a foreign key to its author, which can either be in the American table, or the British one.

How can I implement such foreign key condition in SQLAlchemy?

I'd like to have a single column to handle the link.

My approach so far was to create an abstract class Author, from which both AmericanAuthor and BritishAuthor inherit, and have the foreign key of Book point to the parent.

class Author(Model):
    __abstract__ = True
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

class AmericanAuthor(Author):
    __tablename__ = 'american_author'
    # some other stuff

class BritishAuthor(Author):
    __tablename__ = 'british_author'
    # some other stuff

class Book(Model):
    __tablename__ = 'book'
    title = db.Column(db.String)
    author_id = db.Column(db.Integer, db.ForeignKey("author.id"))

It fails with the error:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'books.author_id' could not find table 'author' with which to generate a foreign key to target column 'id'

Which completely makes sense, considering author is abstract...

Upvotes: 16

Views: 7820

Answers (3)

Josh
Josh

Reputation: 1

You should be able to get around that by using AbstractConcreteBase and primaryjoin.

https://docs.sqlalchemy.org/en/20/orm/inheritance.html

class Base(DeclarativeBase):
    pass

class Author(AbstractConcreteBase, Base):
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    
    @declared_attr
    def books(cls) -> Mapped[List["Book"]]:
        return relationship(
            "Book",
            back_populates="author",
            primaryjoin=lambda: and_(foreign(Book.author_id) == cls.id)
        )


class AmericanAuthor(Author):
    __tablename__ = 'american_author'
    # some other stuff

    __mapper_args__ = {
        "polymorphic_identity": "american",
        "concrete": True,
    }

class BritishAuthor(Author):
    __tablename__ = 'british_author'
    # some other stuff

    __mapper_args__ = {
        "polymorphic_identity": "british",
        "concrete": True,
    }

class Book(Model):
    __tablename__ = 'book'
    title: Mapped[str]
    author_id: Mapped[int]  # Not a foreign key

    author: Mapped["Author"] = relationship(
        back_populates="books",
        primaryjoin="and_(foreign(Book.author_id)==Author.id)"
    )

With this you should be able to do book.author and author.books

Upvotes: 0

Carl Onsjö
Carl Onsjö

Reputation: 196

Although the @property decorator will work in the application it might be better to use the @hybrid_property from the sqlalchemy.ext.hybrid package. In that way you will be able to filter on that property just like any normal attribute.

Your Book class would then look like:

class Book(Model):
    __tablename__ = 'book'
    title = db.Column(db.String)
    american_author_id = db.Column(db.Integer, db.ForeignKey("american_author.id"), nullable=True)
    british_author_id = db.Column(db.Integer, db.ForeignKey("british_author.id"), nullable=True)

    @hybrid_property
    def author_id(self):
        return self.american_author_id or self.british_author_id

Upvotes: 14

lorenzo
lorenzo

Reputation: 622

I think you can't build a relationship with two different tables using the same column.

Try creating two different columns ("american_author_id" and "british_author_id") then make a @property "author" that returns the author that isn't NULL.

This way you can get the author using: mybook.author

Upvotes: 11

Related Questions