changing a max subquery into sqlalchemy query

[outer_edge]

Hi all

I'm trying to translate a sql query into sqlalchemy

http://www.pylonshq.com/pasties/6eab0a062bdbfe30b7310e5b5714f22e

my efforts so far

http://www.pylonshq.com/pasties/21753610908db32e133e2925535e9645

just a side note I should have named many_id something better like
maintable_id so it doesn't confuse people.

I was on #pylons trying to figure out how with no joy.
http://pylonshq.com/irclogs/%23pylons/%23pylons.2009-04-23.log.html#t2009-04-23T10:09:52

I can do this

meta.Session.query(model.History).from_statement(
"SELECT history.*
FROM
(
SELECT max(history.history_id) AS history_id FROM history GROUP BY
history.many_id
)
as max_history join history
ON history.history_id = max_history.history_id and history.action_id
in (foo, ba")"
).all()

Which works but I can't figure how to change the sql to use
sqlalchemy.

The main gotcha is getting the join to work in the subquery.
max_history doesn't have a history_id method. Looking through the
docs.

http://www.sqlalchemy.org/docs/04/ormtutorial.html#datamapping_querying

Half way down the page. The from_statement has something similar but
only returns a single record where I need multiple records. Also in
the example the column is accessed through the c method. When I try
the same no columns are attached to the c method.

Would this indicate that I've done something wrong in the schema? I'm
using 0.4.8 and 0.5.1 on another box.

Hope someone can help me. Thanks

fintan

[Michael Bayer]

s =
select
([func
.max
(history
.c
.history_id
).label('history_id')]).group_by(history.c.many_id).alias('max_history')

sess.query(History).join((s, and_(History.history_id==s.c.history_id,
History.action_id.in_([foo, bar]))))

examples of using query.join() are illustrated at http://www.sqlalchemy.org/docs/05/reference/orm/query.html#sqlalchemy.orm.query.Query.join
.

a very similar example of joining to an aggregate subquery is
illustrated at http://www.sqlalchemy.org/docs/05/ormtutorial.html#using-subqueries
.

[fintan]

Thanks Michael, your a star. I'll read up over the weekend.

   fintan.

2009/4/24 Michael Bayer <mik...@zzzcomputing.com>