SQLModel many-many relationships not working

Question

I've been struggling with this for weeks now. Here's the relevant code:

Base = declarative_base()

class Band_Genre(SQLModel, table=True):
    genre_id: Optional[int] = Field(default=None, foreign_key="FK_Band_Genre_Genre", primary_key=True)
    band_id: Optional[int] = Field(default=None, foreign_key="FK_Band_Genre_Band", primary_key=True)

class Genre(SQLModel, table=True):
    genre_id: int = Field(
        default=None,
        primary_key=True,
        description="Genre ID",
    )
    genre: Optional[str] = Field(
        default=None,
        description="Name of the genre",
    )
    band_links: List["Band"] = Relationship(back_populates='genres', link_model=Band_Genre)

    def __str__(self):
        return f"{self.genre_id}: {self.genre}"
    
class Band(SQLModel, table=True):
    band_id: Optional[int] = Field(
        default=None,
        primary_key=True,
        description="Band ID",
    )
    band_name: Optional[str] = Field(
        default=None,
        description="Name of the band",
    )
    genres: List[Genre] = Relationship(back_populates='bands',link_model=Band_Genre)
    
    def __str__(self):
        return f"{self.band_id}: {self.band_name}"
 

engine = create_engine(
    f"mssql+pyodbc://{uid}:{pwd}@{svr}/{db}?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes",
)

Session = sessionmaker(bind=engine)

session = Session()
statement = select(Band)
results = session.execute(statement)

for r in results.all():
    for g in r.genres:
        print(g)

The goal is to get all the genres when retrieving the band (via FastAPI/Pydantic).

But I keep getting an error:

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Genre.bands - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression

I've tried what feels like every possible combination of SQLAlchemy relationships kwargs in the relationship. Defining only one side of the many-many and a ton of other options, but I can't seem to resolve this. Has anyone else found a solution to this?

It looks like the `bands` relationship in your `Genre` model is missing `link_model=Band_Genre` to link the `Band` and `Genre` models via the association table ("link table"). (ref: [tutorial](https://sqlmodel.tiangolo.com/tutorial/many-to-many/create-models-with-link/)) — Gord Thompson, Apr 25 '23 at 22:38
Thanks @GordThompson, I've tried with and without. But to be fair, I updated with the latest, smallest code possible. I've verified that I can connect and get the proper data directly with SQLAlchemy on it's own, so it's not a DB or connection issue. — MichaelD, Apr 27 '23 at 19:28
You have a couple of issues. The main one is that `foreign_key="FK_Band_Genre_Genre"` should be `foreign_key="genre.genre_id"` (and the same for band.band_id). Also, the name `band_links` is inconsistent (should be `bands`). [This code](https://gist.github.com/gordthompson/28fe8317505e81b4733cd2d8ca84763f) works for me. — Gord Thompson, Apr 27 '23 at 20:19

score 1 · Answer 1 · answered Jul 25 '23 at 11:16

You need to update the foreign_key in the link table Band_Genre to point to the actual ID's of the respective models that is Genre and the Band model.

Also update the back_populates attribute in the band to point band_links in the Genre model

You can refer to this SQLModel tutorial

Base = declarative_base()

class Band_Genre(SQLModel, table=True):
    genre_id: Optional[int] = Field(default=None, foreign_key="genre.genre_id", primary_key=True)
    band_id: Optional[int] = Field(default=None, foreign_key="band.band_id", primary_key=True)

class Genre(SQLModel, table=True):
    genre_id: int = Field(
        default=None,
        primary_key=True,
        description="Genre ID",
    )
    genre: Optional[str] = Field(
        default=None,
        description="Name of the genre",
    )
    band_links: List["Band"] = Relationship(back_populates='genres', link_model=Band_Genre)
    def __str__(self):
        return f"{self.genre_id}: {self.genre}"
    
class Band(SQLModel, table=True):
    band_id: Optional[int] = Field(
        default=None,
        primary_key=True,
        description="Band ID",
    )
    band_name: Optional[str] = Field(
        default=None,
        description="Name of the band",
    )
    genres: List[Genre] = Relationship(back_populates='band_links',link_model=Band_Genre)
    
    def __str__(self):
        return f"{self.band_id}: {self.band_name}"
 

engine = create_engine(
    f"mssql+pyodbc://{uid}:{pwd}@{svr}/{db}?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes",
)

Session = sessionmaker(bind=engine)

session = Session()
statement = select(Band)
results = session.execute(statement)

for r in results.all():
    for g in r.genres:
        print(g)

As an addition to the answer, when you set the FK Field in the `foreign_key` you should use reference to the PK via table name and not ORM model name (i.e. `genre`, not `Genre`). Besides, keep in mind, if you use DB schemas in Postgres or SQL Server you should also mention it in the `foreign_key` (e.g. `Field(default=None, foreign_key="my_schema_name.genre.genre_id"`). — Max Kapustin, Jul 28 '23 at 08:49

SQLModel many-many relationships not working

1 Answers1