subq = session.query(ColumnOrm.object_id, ColumnOrm.revision).\ group_by(ColumnOrm.id, ColumnOrm.revision).\ having(ColumnOrm.revision<=20).subquery()
print session.query(ColumnOrm).join((subq, and_(ColumnOrm.object_id==subq.c.object_id, ColumnOrm.revision==subq.c.revision))) jarrod.ches...@gmail.com wrote: > > 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 -~----------~----~----~----~------~----~------~--~---