Hi,
you could simply get the Bowler objects from the results by saying
results = [r[0] for r in results]
I'm not sure whether you query is correct, though. Usually, you cannot
select columns which are not in the GROUP BY clause or which are not
aggregated - after grouping, you have several bowler_id candidates for
each group, and there's nothing in your query telling the database
which ID you want. MySQL allows this and implies that all ID values
are the same, SQLite might do something similar. Try adding some more
bowlers for each city and see if it still returns the results you
expect.
You can rewrite the query using exists():
b1 = bowlers_table.alias()
b2 = bowlers_table.alias()
results = session.query(Bowler).filter(not_(
exists([b2.c.bowler_id], and_(b2.c.bowler_id != b1.c.bowler_id,
b2.c.city_id == b1.c.city_id, b2.c.highscore >
b1.c.highscore)).correlate(b1)
)).all()
Cheers, Simon
On 10 Nov., 11:35, Ian Charnas <[EMAIL PROTECTED]> 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.
>
> please help!!!
>
> Full example
> ----------------------
>
> # STANDARD BOILERPLATE
> from sqlalchemy import *
> from sqlalchemy.orm import *
> engine = create_engine('sqlite:///:memory:', echo=True,
> strategy='threadlocal')
> Session = scoped_session(sessionmaker(autoflush=False,
> autocommit=False))
> session = Session(bind=engine)
> metadata = ThreadLocalMetaData()
> metadata.bind = engine
>
> # DEFINE TABLES
> bowlers_table = Table('bowler', metadata,
> Column('bowler_id', Integer, primary_key=True),
> Column('name', String(50)),
> Column('highscore', Integer, default=0),
> Column('city_id', None, ForeignKey('city.city_id'))
> )
> cities_table = Table('city', metadata,
> Column('city_id', Integer, primary_key=True),
> Column('name', String(50))
> )
> metadata.create_all()
>
> # DEFINE CLASSES
> class Base(object):
> def __init__(self, **kwargs):
> for key, value in kwargs.iteritems():
> setattr(self, key, value)
> class Bowler(Base):
> pass
> class City(Base):
> pass
>
> # MAP CLASSES
> mapper(City, cities_table)
> mapper(Bowler, bowlers_table, properties={
> 'city': relation(City)
>
> })
>
> # CREATE SAMPLE DATA
> nyc = City(name="New York City")
> michael_bayer = Bowler(name="Michael Bayer", highscore=299, city=nyc)
> big_lebowski = Bowler(name="Jeffrey Lebowsky", highscore=170,
> city=nyc)
>
> cle = City(name="Cleveland")
> ian_charnas = Bowler(name="Ian Charnas", highscore=220, city=cle)
> the_jesus = Bowler(name="Antonio DeJesus", highscore=130, city=cle)
>
> session.add_all([michael_bayer, big_lebowski, ian_charnas, the_jesus])
> session.flush()
>
> # GET HIGH SCORERS BY CITY
> max_score = func.max(Bowler.highscore).label('highest_score')
> results = session.query(Bowler,
> max_score).group_by(Bowler.city_id).all()
>
> # Results contain (Bowler, max_score) tuples
> [(<__main__.Bowler object at 0x139b590>, 170),
> (<__main__.Bowler object at 0x13b20d0>, 130)]
>
> # But I want results to just contain Bowler objects
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
