This seems to work. I'm just hoping to run this past the wisdom of other
users...
SqlAlchemy + PostgreSQL
We have a table called `example` which contains versioned "fields" of a
record, attributed to an owner.
Goal: assemble a record of the most recent field versions for a given owner
class Example():
id = sa.Column(sa.Integer, primary_key=True)
owner_id = sa.Column(sa.Integer, sa.ForeignKey("owner.id"),
nullable=False, )
field_id = sa.Column(sa.Integer, sa.ForeignKey("field.id"),
nullable=False, )
version = sa.Column(sa.Integer, nullable=False, default=0)
text = sa.Column(sa.Text, default='', nullable=True )
result = dbSession.writer.query( model.core.Example )\
.filter(\
model.core.Example.owner_id == 100
)\
.distinct( model.core.Example.field_id )\
.order_by( model.core.Example.field_id.desc() ,
model.core.Example.version.desc() )\
.all()
SQL
SELECT
DISTINCT ON ( field_id )
id , field_id , owner_id , version , text
FROM
example
WHERE
owner_id = 100
ORDER BY
field_id , version DESC;
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
