python - Limiting the subquery with SQLAlchemy -

i have defined few models in sqlalchemy:

class location(base):     __tablename__ = 'location'     __table_args__ = {u'schema': 'location'}      unit_id = column(foreignkey(u'structure.definition.unit_id', ondelete=u'restrict', onupdate=u'cascade'), primary_key=true, nullable=false)     timestamp = column(datetime, primary_key=true, nullable=false)     latitude = column(float)     longitude = column(float)   class definition(base):     __tablename__ = 'definition'     __table_args__ = {u'schema': 'structure'}      unit_id = column(integer, primary_key=true)     name = column(string(90))      location = relationship(u'location') 

and have query:

sub = dbsession.query(location.unit_id, func.max(location.timestamp).label('latest_timestamp')).\     filter(location.latitude != none, location.longitude != none).\     group_by(location.unit_id).\     subquery()  res = dbsession.query(definition).\     join((sub, sub.c.unit_id == definition.unit_id)).\     all() 

the result of query few definition objects possible location each 1 of them. however, i'd make query definition rows, last row locations table each of definition row.

what best way make query, taking in account location table has hundreds of thousands of rows in already?

well, not seem use latest_timestamp subquery @ all, looks complete waste. also, query returns only definition instances.

try instead:

res = dbsession.query(definition, location).\     join(location, definition.location).\     join((sub, and_(sub.c.unit_id == definition.unit_id,                      sub.c.latest_timestamp == location.timestamp)).\     all()   defi, loca in res:     print(defi, loca) 

note: prefer locations instead of location name of relationship.