1、参考https://blog.csdn.net/qq_34989829/article/details/111545430
https://blog.csdn.net/weixin_44737199/article/details/94215679
2、代码
master = aliased(MasterInfo, name='a')
detail = aliased(DetailInfo, name='b')
query = db.session.query(master)
query = query.filter((now >= master.BEGINDATE) & (now <= master.ENDDATE))
if hasattr(body, 'TYPE') and body.TYPE:
query = query.filter(or_(master.TYPE == body.type, master.type == '全部'))
query = (query
.outerjoin(detail, master.ID == detail.MASTERID)
)
or_filter = []
if hasattr(body, 'DST') and body.DST and hasattr(body, 'AST') and body.AST:
or_filter.append(and_((master.RELATION == 2),
(detail.DST == body.DST) &
(detail.AST == body.AST)
)
)
if hasattr(body, 'NAME') and body.NAME:
or_filter.append(and_((master.RELATION == 3),
(detail.NAME == body.NAME)
)
)
if hasattr(body, 'APS') and body.APS:
ap_list = body.APS.split(',')
if ap_list:
or_filter.append(and_((master.APS == 1),
(detail.APS.in_(ap_list))
)
)
if or_filter:
query = query.filter(or_(*or_filter))
query = query.order_by(master.CREATEDATE)
items = query.all()
3、生成的SQL如下:
SELECT ...
FROM [MASTER_INFO] AS a
LEFT OUTER JOIN [DETAIL_INFO] AS b
ON a.[ID] = b.[MASTERID]
WHERE a.[BEGINDATE] <= %(BEGINDATE_1)s
AND a.[ENDDATE] >= %(ENDDATE_1)s
AND (a.[TYPE] = %(TYPE_1)s OR a.[TYPE] = %(TYPE_2)s)
AND (
(a.[RELATION] = %(RELATION_1)s AND b.[DST] = %(DST_1)s AND b.[AST] = %(AST_1)s)
OR a.[RELATION] = %(RELATION_2)s AND b.[NAME] = %(NAME_1)s
OR a.[RELATION] = %(RELATION_3)s AND b.[APS] IN (__[POSTCOMPILE_APS_1])
)
ORDER BY a.[CREATEDATE]
4、sql中and和or的优先级, and的优先级大于or,通俗理解其实or查询其实会把条件分为左右两边来查,python中and和or的优先级也类似,所以or后面的不再需要括号提高优先级。