在SQLAlchemy中排序“黑盒”查询？

Question

我经常使用这样的查询：

def activityQuery(inActivity, inOrder='time'):
    a1 = gDB.session.query(Activity.unitID, Activity.activity, func.max(Activity.occurredAt).label("maxOA")) \
        .group_by(Activity.unitID) \
        .subquery()
    a2 = aliased(Activity, name="a2")
    u = aliased(Unit, name="u")
    q = gDB.session.query(u).select_from(a1) \
        .join(a2, and_(a2.unitID == a1.c.unit_id, a2.occurredAt == a1.c.maxOA)) \
        .join(u, u.id == a2.unitID).filter(a2.activity.in_(inActivity))

    if inOrder == 'time':
        q = q.order_by(a2.occurredAt)
    elif inOrder == 'address':
        q = q.order_by(u.street, u.unitNumber)
    return q

如您所见，我正在传递一个参数以指示所需的排序，但我确实希望在调用activityQuery()之后指定排序。这可能吗？显然，像a2和u这样的符号超出了这个调用的范围。是否有其他方法指定如何对结果查询排序？谢谢

Answer 1

使activityQuery接受一个可调用的as，以便：

def timeOrder(q, u, a1, a2):
    return q.order_by(a2.occurredAt)


def addressOrder(q, u, a1, a2):
    return q.order_by(u.street, u.unitNumber)


def activityQuery(inActivity, inOrder=timeOrder):
    a1 = gDB.session.query(Activity.unitID,
                           Activity.activity,
                           func.max(Activity.occurredAt).label("maxOA")) \
        .group_by(Activity.unitID) \
        .subquery()

    a2 = aliased(Activity, name="a2")
    u = aliased(Unit, name="u")

    q = gDB.session.query(u).select_from(a1) \
        .join(a2, and_(a2.unitID == a1.c.unit_id,
                       a2.occurredAt == a1.c.maxOA)) \
        .join(u, u.id == a2.unitID) \
        .filter(a2.activity.in_(inActivity))

    return inOrder(q, u, a1, a2)

activityQuery(..., inOrder=addressOrder)