How can i implement the following query with sqlalchemy.orm objects?
SELECT columns.*
FROM columns AS col
JOIN
(
SELECT object_id, revision
FROM columns
GROUP BY id, revision
HAVING revision <= 20
) AS lr ON col.object_id = lr.object_id AND col.revision =
lr.revision;
Non syntactically correct example :
from sqlalchemy import schema, types, orm, create_engine
# Setup everything
engine = create_engine('sqlite://:memory:')
metadata = schema.Metadata(bind=engine)
ColumnsTable = schema.Table('columns'
, schema.Column('id', types.Integer, primary_key=True)
, schema.Column('object_id', types.Integer)
, schema.Column('name', types.String)
, schema.Column('revision', types.Integer)
, metadata)
class ColumnOrm (object):
pass
metadata.create_all()
orm.mapper(ColumnOrm, ColumnsTable)
session = orm.create_session(bind=engine, autocommit=True)
# Now for the query
# lr means latest revision
SELECT columns.*
FROM columns AS col
JOIN
(
SELECT object_id, revision
FROM columns
GROUP BY id, revision
HAVING revision <= 20
) AS lr ON col.object_id = lr.object_id AND col.revision =
lr.revision;
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
