How to join multiple tables in sql join using sqlmodel and fastapi

Question

tables.py

class Tool(SQLModel, table=True):
    __tablename__ = 'tools'

    tool_id: Optional[int] = Field(default=None, primary_key=True)
    tool_name : str = Field(sa_column=Column("tool_name", VARCHAR(54),nullable=False))
    tool_description : str = Field(sa_column=Column("tool_description", TEXT , nullable=True))
    tool_guide: str = Field(sa_column=Column("tool_guide", TEXT, nullable=True))

class CountryToolUser(SQLModel, table=True):
    __tablename__ = 'country_tool_user'

    country_id: Optional[int] = Field(default=None, foreign_key='countries.country_id',primary_key=True)
    tool_id :Optional[int] = Field(default=None, foreign_key='tools.tool_id',primary_key=True)
    user_id: Optional[int] = Field(default=None, foreign_key='users.user_id',primary_key=True)

class User(SQLModel, table=True):
    __tablename__ = 'users'
    
    user_id: Optional[int] = Field(default=None, primary_key=True)
    first_name : str = Field(sa_column=Column("first_name", VARCHAR(54),nullable=False))
    last_name : str = Field(sa_column=Column("last_name", VARCHAR(54), nullable=True))
    email : str = Field(sa_column=Column("email", VARCHAR(54), unique=True, nullable=False))
    password : str = Field(sa_column=Column("password", VARCHAR(256), nullable=False))

repository.py

def test(db: Session = Depends(get_db)):
    statement = select(Tool, CountryToolUser).where(Tool.tool_id == CountryToolUser.tool_id)
    results = db.exec(statement)
    return results

How do I join Tool, User and CountryToolUser tables using sqlmodel with more than two where conditions? How do I know what is the query that is used in the background?

score 0 · Answer 1 · answered Mar 26 '23 at 18:31

You should use join and the first model should have all the foreign keys you wish to join.
CountryToolUser can join Tool and User because contains both tool_id and user_id.

    tool_id :Optional[int] = Field(default=None, foreign_key='tools.tool_id',primary_key=True)
    user_id: Optional[int] = Field(default=None, foreign_key='users.user_id',primary_key=True)

If you print the statement you could see what SQLModel will execute.

statement = select(CountryToolUser, Tool, User).join(Tool).join(User)
print(statement)

Finally for multiple where clauses e.x.:

statement = select(CountryToolUser, Tool, User).join(Tool).join(User).where(User.user_id==7).where(Tool.tool_id==1)

check here for more information https://sqlmodel.tiangolo.com/tutorial/where/#where-with-multiple-expressions-using-or

score -1 · Answer 2 · answered Nov 11 '22 at 03:57

You can use comma as a separator between two where conditions, it is like using AND in sql.

def test(db: Session = Depends(get_db)):
    statement = select(Tool, CountryToolUser, User).where(Tool.tool_id == CountryToolUser.tool_id, User.user_id == CountryToolUser.user_id)
    print(statement) #here you can know the sql query formed
    results = db.exec(statement)
    return results

How to join multiple tables in sql join using sqlmodel and fastapi

2 Answers2