On Aug 19, 2014, at 11:12 AM, Ljós Berinn <0na...@gmail.com> 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 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.
