CODEWITHSUNDEEP
sqlalchemy
Rick
Rick

Reputation: 3857

SQLAlchemy select from subquery with two joins

I’ve almost figured out how to translate this query into SQLAlchemy:

select u.* from (select
                    unit_id,
                    activity,
                    max(occurred_at) maxOA
                  from Activity
                    group by unit_id) a1

inner join Activity a2 on
    a2.unit_id = a1.unit_id and a2.occurred_at = a1.maxOA

inner join Unit u on
    u.id = a2.unit_id where a2.activity = 'activateReq';

My tables look like this (gDB is the global variable that holds my SQLAlchemy DB connection):

class Unit(gDB.Model):
    __tablename__ = 'Unit'
    id = gDB.Column('id', gDB.Integer(), primary_key=True)
    owner_id = gDB.Column('owner_id', gDB.ForeignKey('User.id'))
    owner = gDB.relationship(User, primaryjoin=owner_id == User.id)
    unitNumber = gDB.Column('unit_number', gDB.Integer())
    street = gDB.Column('street', gDB.String(255))
    city = gDB.Column('city', gDB.String(255))
    state = gDB.Column('state', gDB.String(2))
    zip = gDB.Column('zip', gDB.String(9))
    serviceInstallDate = gDB.Column('service_install_date', gDB.DateTime())
    activity = gDB.relationship('Activity', backref='unit_id', lazy='select', order_by="desc(Activity.occurredAt)")

    def __repr__(self):
        return '<Unit: {0} {1}>'.format(self.unitNumber, self.street)

class Activity(gDB.Model):
    __tablename__ = 'Activity'
    id = gDB.Column('id', gDB.Integer(), primary_key=True)
    unitID = gDB.Column('unit_id', gDB.ForeignKey('Unit.id'))
    unit = gDB.relationship(Unit, primaryjoin=unitID == Unit.id)
    agentID = gDB.Column('agent_id', gDB.ForeignKey('User.id'))
    agent = gDB.relationship(User, primaryjoin=agentID == User.id)
    occurredAt = gDB.Column('occurred_at', gDB.DateTime())
    activity = gDB.Column('activity', gDB.String(32))
    notes = gDB.Column('notes', gDB.String())

    def __repr__(self):
        return '<Activity: {0} {1}>'.format(self.activity, self.occurredAt)

I’ve gotten this far:

db.session.query(Activity.unitID, Activity.activity, func.max(Activity.occurredAt).label("maxOA"))
    .group_by(Activity.unitID)
    .subquery()

a2 = expression.alias(Activity)
u = expression.alias(Unit)

q = db.session.query(a1)
    .join(a2, and_(a2.c.unit_id == a1.c.unit_id, a2.c.occurred_at == a1.c.maxOA))
    .join(u, u.c.id == a2.c.unit_id).filter(a2.c.activity == 'activateReq')

q.all()

q.all() is returning the right set of rows, but I want the columns of Unit, not of the subquery a1.

Update

I’ve gotten closer: The trick is to query() the desired columns (in this case, all the columns of u), and then select_from() the subquery a1:

q = db.session.query(u).select_from(a1).join(a2, and_(a2.c.unit_id == a1.c.unit_id, a2.c.occurred_at == a1.c.maxOA)).join(u, u.c.id == a2.c.unit_id).filter(a2.c.activity=='activateReq')

The problem now is that the resulting Unit instances don’t have their related User (owner_id/owner) objects; my Jinja template tries to reference the user.owner and I get

'sqlalchemy.util._collections.result object' has no attribute 'owner'

I also have no idea if this is the most concise or performant way to do this.

Upvotes: 0

Views: 2674

Answers (1)

Rick
Rick

Reputation: 3857

Ah! I figured it out. I'm using expression.alias() instead of orm.aliased(). It should be like this:

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 == 'activateReq')

Upvotes: 2

Related Questions