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