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