SqlAlchemy fastApi trying to join many - to

1st table "release_name":

|code|name|

|:---|:--:|

|1---|SOME|

|2---|ANY |

...

2nd table "release_value": |code|name|

|:---|:--:|

|1---|SOME|

|2---|ANY |

...

3rd Is a table to make a join 2 tables above "release_code_name":

|release_name_code|release_value_code|

|:----------------|:----------------:|

|1----------------|2-----------------|

|2----------------|3-----------------|

I'm using SQLAlchemy with FastApi. What I need to do is to join 2 tables in one by 3rd map table.

What I've tried at it doesn't print me what I need:

Link = Table(
    "release_code_name",
    Base.metadata,
    Column("release_name_code", Integer, ForeignKey("release_name_code.code"), primary_key=True),
    Column("release_value_code", Integer, ForeignKey("release_value_code.code"), primary_key=True),
    PrimaryKeyConstraint("release_name_code", "release_value_code")
)



class ReleaseNames(Base):
    __tablename__="release_name"

    code = Column(Integer, primary_key=True)
    name = Column(String, unique=True, nullable=False)
    build_name = relationship("BuildNames", secondary="release_code_name", back_populates="release_name")

class BuildNames(Base):
    __tablename__ = "release_value"

    code = Column(Integer, primary_key=True)
    name = Column(String, unique=True, nullable=False)
    release_name = relationship(ReleaseNames, secondary="release_code_name", back_populates="build_name")

@router.get("/")
async def build_names_by_release(release_name: str, db: Session = Depends(get_database_session)):
   result = db.query(ReleaseNames).join(BuildNames, ReleaseNames.build_name)
   return result

I think I have an issue on joining tables in controller function.

CodePudding user response：

Inside your relationship, secondary doesn't take a string but the variable containing your Table:

build_name = relationship("BuildNames", secondary=Link, back_populates="release_name")

release_name = relationship("ReleaseNames", secondary=Link, back_populates="build_name")

In the creation of your table, in ForeignKey, you pass a wrong parameter. Indeed, you gave it release_name_code.code instead of release_name.code . You want to link your Column to the Table release_name and not to your column release_name_code (that you are creating right now).

release_name_code is the name of the column you are creating. release_name is your Table ReleaseNames.

So replace the ForeignKey of your Table as follows:

Column("release_name_code", Integer, ForeignKey("release_name.code"), primary_key=True),
Column("release_value_code", Integer, ForeignKey("release_value.code"), primary_key=True),