sqlalchemy: does relation.foreignKeys support for multiple list of foreignKeys?

18 Views Asked by At

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

0

There are 0 best solutions below