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
.
On Apr 24, 2009, at 5:17 AM, outer_edge wrote:
>
> 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
> >
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
