On Jul 10, 2010, at 5:17 AM, Daniel wrote:
> Hi,
>
> I'm trying to translate this sql expression:
>
> (select max(id) as max_id from votes group by user_id, idea_id) as m
> left join votes on m.max_id=votes.id
>
> Into python code. So far I have:
>
> s = Session()
> q =
> s.query(sa.func.max_(Vote.id).label('max_id')).group_by(Vote.idea_id,
> Vote.user_id)
>
> This is where I get stuck. If I just wanted a join I could do:
>
> s = Session()
> q =
> s.query(sa.func.max_(Vote.id).label('max_id')).group_by(Vote.idea_id,
> Vote.user_id).subquery()
> s.query(Vote).join((q, q.c.max_id==Vote.id))
>
> However, because I need an outer join the statements are back-to-
> front. A right join would work although I think you left them out on
> idealogical grounds. If I try reversing the clauses I get SQL errors
> due to lack of aliasing. Do you have any ideas? (I'm working on mysql)
you'd probably want to use select_from() in conjunction with orm.outerjoin().
its the last example at
http://www.sqlalchemy.org/docs/ormtutorial.html#querying-with-joins .
you also might be able to say query(Vote).outerjoin((Vote,
subquery.c.max_id==Vote.id)).
not sure what the "lack of aliasing" error is, subquery() returns an Alias()
object.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
