Thanks Michael, your a star. I'll read up over the weekend.
fintan.
2009/4/24 Michael Bayer <mike...@zzzcomputing.com>
>
> 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
-~----------~----~----~----~------~----~------~--~---
