Adding multiple foreign keys from same model (FastAPI and SqlAlechemy)

Question

I am trying to have two foreign keys from User table inside Ban table. Here is how I did it:

class Ban(Base):
    __tablename__ = "ban"

    ban_id = Column(Integer, primary_key=True, index=True)
    poll_owner_id = Column(Integer)
    banned_by = Column(String ,  ForeignKey('user.username', ondelete='CASCADE', ), unique=True)
    user_id = Column(Integer,  ForeignKey('user.user_id', ondelete='CASCADE', ))
    updated_at = Column(DateTime)
    create_at = Column(DateTime)


    ban_to_user = relationship("User", back_populates='user_to_ban', cascade='all, delete')

and User table:

class User(Base):
    __tablename__ = "user"

    user_id = Column(Integer, primary_key=True, index=True)
    username = Column(String, unique=True)
    email = Column(String)
    create_at = Column(DateTime)
    updated_at = Column(DateTime)

    user_to_ban = relationship("Ban", back_populates='ban_to_user', cascade='all, delete')

When I try to run a query to fetch all users like this:

@router.get('/all')
async def get_all_users(db:Session = Depends(get_db)):
    return db.query(models.User).all()

I get this error:

sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'mapped class User->user'. Origina
l exception was: Could not determine join condition between parent/child tables on relationship User.user_to_ban - 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.

I did the relationship between them as you can see but it states that there is problem between them. If needed I can show you how I did migration for my db using alembic if that is possible cause or is there a cleaner and better way to do this.

Why two foreign keys to the same table ? If you can locate the User record with user_id why also FK username which depends on user_id ? — ljmc
– ljmc, Commented Jan 4, 2023 at 14:33
Ah I think I understand, one is the user concerned by the ban and the other is the user that enacted the ban. In that case, you need to specify how to join the tables, because SQLAlchemy doesn't know which of these attributes to join on. — ljmc
– ljmc, Commented Jan 4, 2023 at 14:41
@ljmc so the user.user_id and user.username is not sufficient? How can I specify it even further? — Vedo
– Vedo, Commented Jan 4, 2023 at 14:48

ljmc · Accepted Answer · 2023-01-04 14:57:46Z

You can have several foreign keys to a single table, like in your case for banned user and banned_by user.

You just need to disambiguate, which ForeignKey for which relationship (docs):

class Ban(Base):
    __tablename__ = "ban"
    id = Column(Integer, primary_key=True)
    banned_user_id = Column(Integer, ForeignKey("user.id"))  # for banned_user relationship
    banned_by_user_id = Column(Integer, ForeignKey("user.id"))  # for banned_by relationship
    banned_user = relationship("User", foreign_keys=[banned_user_id], back_populates="bans")
    banned_by = relationship("User", foreign_keys=[banned_by_user_id])

Full demo:

from sqlalchemy import (
    Column,
    ForeignKey,
    Integer,
    String,
    create_engine,
    select,
)
from sqlalchemy.orm import Session, declarative_base, relationship

Base = declarative_base()


class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True)
    username = Column(String, unique=True)
    bans = relationship(
        "Ban",
        back_populates="banned_user",
        foreign_keys="Ban.banned_user_id",
    )


class Ban(Base):
    __tablename__ = "ban"
    id = Column(Integer, primary_key=True)
    banned_user_id = Column(Integer, ForeignKey("user.id"))
    banned_by_user_id = Column(Integer, ForeignKey("user.id"))
    banned_user = relationship(
        "User", foreign_keys=[banned_user_id], back_populates="bans"
    )
    banned_by = relationship("User", foreign_keys=[banned_by_user_id])


engine = create_engine("sqlite://", echo=True, future=True)

Base.metadata.create_all(engine)

spongebob = User(username="spongebob")
patrick = User(username="patrickstarr")

spongebob_bans_patrick = Ban(banned_by=spongebob, banned_user=patrick)

with Session(engine) as session:
    session.add_all(
        [
            spongebob,
            patrick,
            spongebob_bans_patrick,
        ]
    )
    session.commit()

with Session(engine) as session:
    result = session.scalars(select(Ban)).first()
    print(
        "User:",
        result.banned_user.username,
        "was banned by User:",
        result.banned_by.username,
    )

# User: patrickstarr was banned by User: spongebob

Collectives™ on Stack Overflow

Adding multiple foreign keys from same model (FastAPI and SqlAlechemy)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related