Thanks for quick reply Michael!
I will try to play with query you provided in a minute, but just to ask, I
was reading about this 'eager loading' stuff... Is that maybe the thing I
should use since this Location table is getting bigger and bigger every
hour (10000+ or so rows) and I should optimize the query as much as
possible... Also, there is a big chance that Definition table will also get
extremely large and in one moment I will have to load all data from
Definition and join it with last timestamp from Location...
Thank you one more time, will report about my progress :)
On Tuesday, August 19, 2014 4:19:13 PM UTC, Michael Bayer wrote:
>
>
> On Aug 19, 2014, at 11:12 AM, Ljós Berinn <0na...@gmail.com <javascript:>>
> wrote:
>
> 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!
>
>
> these queries can be tricky but there’s a form for the best way to do it,
> which is to get the identifier for the most recent Location using max():
>
> max_loc_id = session.query(func.max(Locations.id).label(‘location_id’),
> Locations.unit_id).group_by(Locations.unit_id).subquery()
>
> session.query(Definitions, Locations).join(max_loc_id,
> max_loc_id.c.unit_id == Definitions.ship_id).join(Locations,
> and_(Locations.id == max_loc_id.c.location_id, Locations.unit_id ==
> Definitions.ship_id))
>
>
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
