如何使用sqlmodel和fastapi在sql联接中联接多个表

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))

存储库.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

如何使用具有两个以上where条件的sqlmodel连接Tool、User和CountryToolUser表？我如何知道在后台使用的查询是什么？

Answer 1

您应该使用联接，并且第一个模型应该具有您希望联接的所有外键
CountryToolUser可以加入Tool和User，因为它同时包含Tool_id和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)

如果您打印该语句，您可以看到SQLModel将执行什么。

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

最后，对于多个where子句e.x.:

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

查看此处了解更多信息https://sqlmodel.tiangolo.com/tutorial/where/#where-具有使用或的多个表达式

Answer 2

您可以使用逗号作为两个where条件之间的分隔符，这就像在sql中使用AND一样。

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