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 -~----------~----~----~----~------~----~------~--~---