Gregg Lind wrote:
>
> I have read over
> http://www.sqlalchemy.org/docs/05/ormtutorial.html#using-subqueries
> and http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg11439.html,
> but I'm having trouble putting the pieces together.
>
> In the demo() below, I want to find the row in the database with the
> max for every unique combination of Route(target,startpoint,ts). The
> code I have there *works*, but doesn't seem to use any subquery magic
> at all, nothing from 'correlated' subqueries. What might I be
> missing?
im assuming you're using MySQL since the GROUP BY below doesn't
accommodate every column in the subquery (would be rejected by most DBs).
youll want to query each column individually that is part of what you are
grouping by. i think you also need to use func.max() here and not
func.min(). the join of the subquery to parent table is then probably
just on hop_id. no "correlation" of subquery is needed either since you
are intersecting two complete sets together (all routes intersected with
all "max hop id" routes grouped by x, y, z).
>
> Thanks!
>
> Gregg L.
> ----------
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy import MetaData
> from sqlalchemy import Table, Column, Integer, String
> from sqlalchemy import create_engine
> from sqlalchemy.orm import sessionmaker
> from sqlalchemy import func
>
> Base = declarative_base()
>
> class Route(Base):
> __tablename__ = 'route'
> target = Column(String, nullable=False, primary_key=True)
> hop_id = Column(Integer, nullable=False, primary_key=True)
> ts = Column(Integer, nullable=False, primary_key=True)
> startpoint = Column(String, nullable=False, primary_key=True)
> # a bunch of other fields
> #data = Column(String, nullable=True, primary_key=False)
> #...
> def __repr__(self):
> return "%s %s %s %s" %(self.target, self.hop_id, self.ts,
> self.startpoint)
>
> connstring='sqlite:///:memory:'
> engine = create_engine(connstring, echo=False)
> session = sessionmaker(bind=engine, autoflush=False, autocommit=False)()
> Base.metadata.bind = engine
> Base.metadata.create_all()
>
> samples = [
> ('T1',1,1000,'S1'),
> ('T1',2,1000,'S1'),
> ('T1',3,1000,'S1'),
> ('T1',1,1000,'S2'),
> ('T1',2,1000,'S2'),
> ('T2',1,1000,'S1'),
> ('T2',2,1000,'S1'),
> ('T2',3,1000,'S1'),
> ('T2',4,1000,'S1'),
> ('T2',1,1500,'S1')]
>
> def demo():
> for t,h,ts,s in samples :
> session.add(Route(target=t,hop_id=h,ts=ts,startpoint=s))
>
> session.flush()
> session.commit()
> # row in the database with the max_hop for every unique
> combination of Route(target,startpoint,ts)
> sq = session.query(Route,func.min(Route.hop_id).label('max_hop'))
> sq = sq.group_by(Route.ts,Route.startpoint,Route.target).subquery()
> q = session.query(Route,sq.c.max_hop)
> q = q.filter(Route.target==sq.c.target)
> q = q.filter(Route.startpoint == sq.c.startpoint)
> q = q.filter(Route.hop_id == sq.c.hop_id)
> q.all()
>
>
> ----------------------------
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
