I have defined two models in my Pyramid app which are referencing the
existing database:
class Locations(Base):
__tablename__ = 'Location'
__table_args__ = {u'schema': 'Locations'}
Unit_id = Column(ForeignKey(u'Structure.Definition.Ship_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 Definitions(Base):
__tablename__ = 'Definition'
__table_args__ = {u'schema': 'Structure'}
Ship_id = Column(Integer, primary_key=True)
Name = Column(String(90))
loc = relationship("Locations", backref=backref('Definition'))
And I am using this 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()
As a result, I get 5 Definition objects (since there's 5 rows in that
table) and each of them has *all *possible Location records which is a bit
overkill at the moment since Location table has hundreds of thousands of
rows.
What I would like to get as a result instead is *only the last Location (by
timestamp) for each of Definition records*.
I tried to play around with aliased
<http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#selecting-entities-from-subqueries>
but
for some reason there is no way to get only the last timestamp for each of
my Definition object.
I ended up with query like this:
sub = DBSession.query(Locations).\
filter(Locations.Latitude != None, Locations.Longitude != None).\
order_by(desc(Locations.Timestamp)).\
limit(1).\
subquery()
localias = aliased(Locations, sub)
q = DBSession.query(Definitions, localias).\
join((localias, Definitions.loc)).\
all()
but now I get only one Definition object with its last timestamp which is
also not exactly the thing I need in the end...
I'm a bit confused what am I doing wrong here, so any tip/help/suggestion
is extremely appreciated! Thanks a lot!
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
