链表查询

join方法允许你指定两个或多个表之间的连接条件，并返回一个新的查询对象，该对象包含了连接后的结果。

内连接

from sqlalchemy import join

# 使用join函数
query = db.session.query(User, Order).join(Order, User.id == Order.user_id)
results = query.all()

# 或者使用Query对象的join方法
query = db.session.query(User, Order).join(Order, User.id == Order.user_id)
results = query.all()

# 遍历结果
for user, order in results:
    print(user.username, order.order_id)  # 假设Order表有一个order_id字段

左连接

query = db.session.query(User, Order).outerjoin(Order, User.id == Order.user_id)
results = query.all()

# 遍历结果，注意这里可能有些User没有对应的Order
for user, order in results:
    if order is not None:
        print(user.username, order.order_id)
    else:
        print(user.username, "No orders")
 

通过左连接，查询dialogue_detail和detail_eval表；

conditions = [DialogueDetail.dialog_id == dialog_id,DialogueDetail.create_by == get_user_id()]
dialog_detail_fields = ["id", "dialog_id", "content", "dialog_role","user_source", "create_by", "create_time", "is_delete"]
detail_eval_fields = ["comment_type", "feedback_type", "feedback_content"]
query = (db.session.query(*[getattr(DialogueDetail, f1) for f1 in dialog_detail_fields],*[getattr(DialogueDetailEvaluation, f2) for f2 in detail_eval_fields]).join(DialogueDetailEvaluation,DialogueDetailEvaluation.dialog_detail_id == DialogueDetail.id,isouter=True).filter(*conditions).order_by(DialogueDetail.create_time))tmp_all = query.all()

多表连接

# 假设还有第三个表Product，Order表有一个product_id字段指向Product表的id字段
query = db.session.query(User, Order, Product).join(Order, User.id ==Order.user_id).join(Product, Order.product_id == Product.id)
results = query.all()

# 遍历结果
for user, order, product in results:
    print(user.username, order.order_id, product.name)  # 假设Product表有一个name字段
 

参考：

如何在SQLAlchemy中实现多表联合查询_sqlalchemy 对子查询join-CSDN博客