I am trying to figure out how to implement a correlated subquery with ORM. One of the complexities is that the query has a table that correlates to itself and I can't figure out how to do that aliasing within ORM queries.
In a recent thread, http://groups.google.com/group/sqlalchemy/browse_thread/thread/c010ac1f326dbb2b Richie Ward asked about using subquery to find max item within a group of items Given this class class Content(Base): __tablename__ = 'content' revision_id = Column(Integer, primary_key=True) modulename = Column(Unicode(256)) content = Column(Unicode(102400), default='') summary = Column(Unicode(256)) created = Column(DateTime, default=datetime.now) for each modulename, select the the object with highest revision_id and non-blank content and the solution was something like this revision_ids = session.query(func.max(Content.revision_id)).\ filter(Content.content != '').group_by(Content.modulename).subquery() pages = session.query(Content.modulename, Content.revision_id, Content.content).\ filter(Content.revision_id.in_(revision_ids)).order_by(Content.modulename) The generated SQL (somewhat cleaned up for readability) is SELECT c1.modulename, c1.revision_id, c1.content FROM content c1 WHERE c1.revision_id IN (SELECT MAX(c2.revision_id) FROM content c2 WHERE c2.content != '' GROUP BY c2.modulename) ORDER BY c1.modulename In a very large database with a more complex query and depending on the underlying database engine, a correlated subquery will likely perform better, so I want to build this SQL using ORM syntax. SELECT c1.modulename, c1.revision_id, c1.content FROM content c1 WHERE c1.revision_id = (SELECT MAX(c2.revision_id) FROM content c2 WHERE c2.modulename = c1.modulename AND c2.content != '') ORDER BY c1.modulename How do we do that? None of my attempts manage to get the subquery where clause to include the correlation c2.modulename = c1.modulename. Maybe need to use alias somehow? -- Mike Conley --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
