It sounds like you need a subquery that finds last order then construct a query joining clients and orders to the subquery.
http://www.sqlalchemy.org/docs/05/ormtutorial.html?highlight=subquery#using-subqueries Something like this: class Client(Base): __tablename__ = 'client' id = Column(Integer, primary_key=True) name = Column(String) class Order(Base): __tablename__ = 'order' onum = Column(Integer, primary_key=True) odate = Column(Integer) cid = Column(Integer, ForeignKey('client.id')) amt = Column(Integer) client = relation('Client', backref='orders') C=Client O=Order lasto=session.query(O.cid, func.max(O.onum).label('lastord')).\ group_by(O.cid).subquery() q = session.query(O.onum, O.odate, O.amt, C.name).join(C) q = q.join((lasto, and_(lasto.c.cid==O.cid, lasto.c.lastord==O.onum))) If needed this can probably be optimized for efficiency -- Mike Conley On Fri, Jul 24, 2009 at 4:32 AM, Noufal <nou...@gmail.com> wrote: > > Hello everyone, > I've been using sqlalchemy with elixir for a legacy project for a > while now and recently needed to write some more than trivial queries. > I have the default elixir generated mappers but using only them forces > me to do some data processing in my app rather than in the database. > This makes it slow. > > What I need to do is something like this. I have a table of clients > which contains id, name and a couple of other fields. This is linked > to an orders table through a OneToMany relationship (each client had > multiple orders). Every row of the order table contains some details > like date, type etc. and a backlink (foreign key) back to the clients > table. > > I need to query out a list of clients and their latest orders. My > mappers are not set up for this and so I need to do a manual query. > Can someone suggest how I'd do this in sqlalchemy? > > thanks > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
