Simon, Michael, thank you!
Simon, yes you were totally right, my query was totally wrong! I was
up all night trying to get some code working, and at 5am I was getting
a little fuzzy. I'd like to use that as my excuse ;-)
What I ended up doing this morning was doing a simple query with max
and group_by that finds the highest score in each city, and then
joined that with Bowler. The resulting SQL looks great. Thanks for
your help!
max_score = func.max(Bowler.highscore).label('city_highscore')
city_highscores = session.query(Bowler.city_id,
max_score).group_by(Bowler.city_id).subquery()
best_bowlers = session.query(Bowler).join(
(highest_scores,
and_(
Bowler.city_id==city_highscores.c.city_id,
Bowler.highscore==city_highscores.c.city_highscore
)
)
)
Hope this helps someone searching on the list!
-Ian
On Nov 10, 10:27 am, Michael Bayer <[EMAIL PROTECTED]> wrote:
> On Nov 10, 2008, at 5:35 AM, Ian Charnas wrote:
>
>
>
>
>
> > Hello Alchemy Land!
>
> > If I have a simple test-case with Bowler objects and City objects, and
> > I want to use func.max and group_by in order to find the highest
> > scorers in each city... I might do something like this:
>
> > max_score = func.max(Bowler.highscore).label('highest_score')
> > results = session.query(Bowler,
> > max_score).group_by(Bowler.city_id).all()
>
> > So this works as I'd expect, and 'results' now contains
> > (Bowler,max_score) tuples... but what I really want is to have a
> > query that just returns Bowler objects, and not these tuples. Is
> > there a way to get rid of that 'max_score' column from the result
> > set? I've been at this for hours, I bet it's really simple but I just
> > can't find it.
>
> hey Ian -
>
> any chance you can just use the func.max() at the end of the Query
> using the values() method ? that way its just an ad-hoc thing.
> Otherwise there's no official way to "remove" an entity from an
> existing Query's list of entities.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
