赞
踩
示例:
- 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))
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。