when I run it i get the correct: SELECT content.modulename AS content_modulename, content.revision_id AS content_revision_id, content.content AS content_content FROM content WHERE content.revision_id IN (SELECT max(content.revision_id) AS max_1 FROM content GROUP BY content.modulename) AND content.content != ? ORDER BY content.modulename
it works identically regardless of subquery() being present. are you on the latest version ? On Sep 6, 2009, at 4:13 PM, Richie Ward wrote: > > I would like to convert this query to SQLAlchemy: > SELECT revision_id, modulename, content > > FROM content > > WHERE revision_id IN ( > > SELECT MAX(revision_id) FROM content c > > GROUP BY modulename > > ) > > AND content != '' > > ORDER BY modulename > > with this table: > CREATE TABLE content ( > > revision_id INTEGER NOT NULL, > > modulename VARCHAR(256), > > content VARCHAR(102400), > > summary VARCHAR(256), > > created TIMESTAMP, > > PRIMARY KEY (revision_id) > > ) > > or: > > class Content(DeclarativeBase): > __tablename__ = 'content' > > revision_id = Column(Integer, primary_key=True) > modulename = Column(Unicode(256)) > content = Column(Unicode(102400), default='') > summary = Column(Unicode(256)) > users = relation(User, secondary=ContentUser.__table__, > backref='content') > created = Column(DateTime, default=datetime.now) > > I had a go at it myself and heres what I managed to do: > revision_ids = DBSession.query(func.max(Content.revision_id)).group_by > ( > > Content.modulename).subquery() > > pages = DBSession.query(Content.modulename, Content.revision_id, > > Content.content).filter(and_( > > Content.revision_id.in_(revision_ids), > > Content.content != '')).order_by( > > Content.modulename) > > It worked (as in does not error out) if I remove .subquery() but then > pages does not work as expected, I think I need to use a subquery to > make it work 100%. > > Heres the error: > OperationalError: (OperationalError) near "SELECT": syntax error > u'SELECT content.modulename AS content_modulename, content.revision_id > AS content_revision_id, content.content AS content_content \nFROM > content, (SELECT max(content.revision_id) AS max_1 \nFROM content > GROUP BY content.modulename) AS anon_1 \nWHERE content.revision_id IN > SELECT max(content.revision_id) AS max_1 \nFROM content GROUP BY > content.modulename AND content.content != ? ORDER BY > content.modulename' [''] > > There is a forum thread on my favorable forum where I asked how to > design the query, as I got a bit stuck on the theory behind such a > complex query. This should give you a idea of what I am trying to > achieve. > http://forums.overclockers.co.uk/showthread.php?t=18050830 > > Thanks alot for your help :) > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
