示例:
from sqlalchemy import func
from sqlalchemy.orm import aliased
def get_data(request_data):
request_data = request_data if request_data else {}
dep = request_data.get('DEP', '')
arr = request_data.get('ARR', '')
AT = request_data.get('TYPE', '')
page_num = request_data.get('PAGE_NUM', None)
page_index = request_data.get('PAGE_INDEX', None)
m = aliased(Table1, name='a')
f = aliased(Table2, name='b')
a1 = aliased(Table3, name='a1')
a2 = aliased(Table3, name='a2')
m_query = db.session.query(f)
query = m_query.join(m, m.ID == f.ID) \
.join(a1, m.DEP == a1.ACODE)\
.join(a2, m.ARR == a2.ACODE)\
.add_entity(f)
if dep:
query = query.filter(m.DEP == dep)
if arr:
query = query.filter(m.ARR == arr)
if AT:
ATs = AT.split(',')
if ATs:
query = query.filter(f.AT.in_(ATs))
# field_list = Table1.get_aliased_fields(m)
# field_list.append(a1.CITY.label('DCITY'))
# field_list.append(a2.CITY.label('ACITY'))
# query = query.with_entities(
# *field_list
# ).order_by(m.DEP.asc(), m.ARR.asc(),
m.NAME.asc(), f.AT.asc())
query = query.with_entities(
*Table1.__mapper__.attrs,
func.concat(m.DEP, '-', m.ARR).label('AL'),
(a1.CT + '-' + a2.CT).label('AL_NAME'),
a1.CT.label('DEP_CT'),
a2.CT.label('ARR_CT'),
*Table2.__mapper__.attrs
).order_by(m.DEP.asc(), m.ARR.asc(),
m.NAME.asc(), f.AT.asc())
if page_num:
if (not page_index) or (page_index <= 0):
page_index = 1
pagination = query.paginate(page_index, per_page=page_num, error_out=False)
data = [dict(zip(item._fields, item)) for item in pagination.items]
return dict(items=data, page=pagination.page,
pages=pagination.pages, total=pagination.total)
else:
result = query.all()
data = [dict(zip(item._fields, item)) for item in result]
return dict(items=data, page=1, pages=1, total=len(data))