python - Specify default join path for multiple foreign keys between tables - Stack Overflow

admin管理员组

文章数量:1125956

The code

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)


class Asset(Base):
    __tablename__ = 'asset'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relationship(User, foreign_keys=[user_id])

    created_by = Column(Integer, ForeignKey(f"user.id"))
    updated_by = Column(Integer, ForeignKey(f"user.id"))


items = db.execute(select(User).join(Asset)).all()

throws an error:

sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'user' and 'asset'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.

Obvious way to solve this is to specify in the statement which foreign key to use:

items = db.execute(select(User).join(Asset.user)).all()

But is there a way to solve this by changing models?

I would like something like:

    created_by = Column(Integer, ForeignKey(f"user.id", do_not_use_for_joins=True))
    updated_by = Column(Integer, ForeignKey(f"user.id", do_not_use_for_joins=True))

or at least:

    user = relationship(User, foreign_keys=[user_id], use_as_default_when_no_key_specified=True)

Because the fields created_by and updated_by are added to many tables and much code is already written.

This could be an ideological question. Is it normal not to mark created_by and updated_by as foreign keys in models (but leave them like that in the database)?

The code

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)


class Asset(Base):
    __tablename__ = 'asset'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relationship(User, foreign_keys=[user_id])

    created_by = Column(Integer, ForeignKey(f"user.id"))
    updated_by = Column(Integer, ForeignKey(f"user.id"))


items = db.execute(select(User).join(Asset)).all()

throws an error:

sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'user' and 'asset'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.

Obvious way to solve this is to specify in the statement which foreign key to use:

items = db.execute(select(User).join(Asset.user)).all()

But is there a way to solve this by changing models?

I would like something like:

    created_by = Column(Integer, ForeignKey(f"user.id", do_not_use_for_joins=True))
    updated_by = Column(Integer, ForeignKey(f"user.id", do_not_use_for_joins=True))

or at least:

    user = relationship(User, foreign_keys=[user_id], use_as_default_when_no_key_specified=True)

Because the fields created_by and updated_by are added to many tables and much code is already written.

This could be an ideological question. Is it normal not to mark created_by and updated_by as foreign keys in models (but leave them like that in the database)?

• python
• sqlalchemy
• orm

Share Improve this question Follow asked 2 days ago julia ussjulia uss 16111 silver badge44 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

The solution was to abandon foreign keys, and use relationship fields with primaryjoin

class Asset(Base):
    __tablename__ = 'asset'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relationship(User, foreign_keys=[user_id])

    created_by = Column(Integer)
    updated_by = Column(Integer)

    creator = relationship(User, foreign_keys=[created_by], primaryjoin=created_by == User.id)
    updater = relationship(User, foreign_keys=[updated_by], primaryjoin=updated_by == User.id)

本文标签： pythonSpecify default join path for multiple foreign keys between tablesStack Overflow