I'm using sqlalchemy with flask app with PostgresDB One of my tables has multiple ForeignKeys to same table PrimaryKey Need to represent children and better in single query Here is my table
class Codes(Model):
__tablename__ = "codes"
code_id = Column(Integer, primary_key=True)
code_name = Column(String, unique=True)
with_1 = Column(Integer, ForeignKey("codes.code_id"))
with_2 = Column(Integer, ForeignKey("codes.code_id"))
with_3 = Column(Integer, ForeignKey("codes.code_id"))
child_1 = relationship("Codes", back_populates="code_1", foreign_keys=[with_1])
child_2 = relationship("Codes", back_populates="code_2", foreign_keys=[with_2])
child_3 = relationship("Codes", back_populates="code_3", foreign_keys=[with_3])
code_1 = relationship(
"Codes", back_populates="child_1", remote_side=[code_id], foreign_keys=[with_1]
)
code_2 = relationship(
"Codes", back_populates="child_2", remote_side=[code_id], foreign_keys=[with_2]
)
code_3 = relationship(
"Codes", back_populates="child_3", remote_side=[code_id], foreign_keys=[with_3]
)
But it makes 3 queries for each child
My expectation use like next code
class Codes(Model):
__tablename__ = "codes"
code_id = Column(Integer, primary_key=True)
code_name = Column(String, unique=True)
with_1 = Column(Integer, ForeignKey("codes.code_id"))
with_2 = Column(Integer, ForeignKey("codes.code_id"))
with_3 = Column(Integer, ForeignKey("codes.code_id"))
children = relationship("Codes", back_populates="parent", foreign_keys=[with_1,with_2,with_3])
parent = relationship(
"Codes", back_populates="children", remote_side=[code_id], foreign_keys=[with_1,with_2,with_3]
)
But it raises next error: Specify the 'foreign_keys' argument, providing a list which already provided
sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'Mapper[Codes(codes)]'. Original exception was: Could not determine join condition between parent/child tables on relationship Codes.children - 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.
Not sure what exactly is wrong here