CODEWITHSUNDEEP
pythonsqlalchemy
Rick
Rick

Reputation: 3857

Sorting a "black box" query in SQLAlchemy?

I use a query like this one a lot:

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

As you can see, I'm passing an argument to indicate the desired sort, but I'd really like to just specify the sort after calling activityQuery(). Is this possible? Obviously, symbols like a2 and u are out of scope outside of this call. Is there another way to specify how to order the resulting query? Thanks!

Upvotes: 0

Views: 31

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

Make activityQuery accept a callable as inOrder: 

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)

Upvotes: 1

Related Questions