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
-~----------~----~----~----~------~----~------~--~---
