On Sep 12, 2009, at 1:35 PM, Mike Conley wrote: > 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?
subquery() returns a select() with correlate turned off. Use either the correlate() method on Query or on the returned select object to set up correlate explicitly (arguably how it should be for all selects...). --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
