Good point. But consider how SelectResults is used. My use case is for
unit testing.
query = session.query(MyClass)
sr = SelectResults(query)
sr = sr.filter(MyClass.c.price <= 800)
sr = sr.order_by(asc(MyClass.c.price))
# Check query results.
assert sr.max(MyClass.c.price) <= 800
The check seems reasonable to me without thinking about the SQL
involved. I consider the count, min, max, sum, avg, and list methods to
be applied after a query is completely constructed (all filtering is
done) because those methods don't return another SelectResults instance
to work with. In the case of min, max, an integer is returned. It seems
to me these should be working on a subquery so that the above would
generate this:
SELECT MAX(price) FROM
(SELECT id, name, price FROM my_table
WHERE price <= 800
ORDER BY price)
And this query as generated in the test case:
>>SELECT max(t3.t1_id)
>>FROM (SELECT t1.id AS t1_id, t1.name AS t1_name, t2.id AS t2_id,
>>t2.id AS t2_name
>>FROM t1, t2
>>WHERE t1.id = t2.t1_id) AS t3 ORDER BY t3.t1_id ASC, t3.t2_name
>>DESC, t3.t1_name ASC
Should look like this:
SELECT max(t3.t1_id)
FROM (SELECT t1.id AS t1_id, t1.name AS t1_name, t2.id AS t2_id,
t2.id AS t2_name
FROM t1, t2
WHERE t1.id = t2.t1_id ORDER BY t3.t1_id ASC, t3.t2_name
DESC, t3.t1_name ASC
) AS t3
Again, SelectResults methods max, min, avg, etc should be applied on a
subquery or a similar mechanism. If they need to be applied within,
then they can be included in filter statements.
Please tell me if there are problems with this logic.
Randall
Michael Bayer wrote:
> why would you use order by with max ? id say thats an invalid
> request to start with.
>
> On Jul 21, 2006, at 2:33 AM, Randall Smith wrote:
>
>
>>Calling min or max on a SelectResults instance that has ordering,
>>generates invalid SQL. The attached example runs on SQLite, but
>>will not run on Postgresql. Here is the query it generates:
>>
>>SELECT max(t3.t1_id)
>>FROM (SELECT t1.id AS t1_id, t1.name AS t1_name, t2.id AS t2_id,
>>t2.id AS t2_name
>>FROM t1, t2
>>WHERE t1.id = t2.t1_id) AS t3 ORDER BY t3.t1_id ASC, t3.t2_name
>>DESC, t3.t1_name ASC 'SELECT max(t3.t1_id) \nFROM (SELECT t1.id AS
>>t1_id, t1.name AS t1_name, t2.id AS t2_id, t2.id AS t2_name \nFROM
>>t1, t2 \nWHERE t1.id = t2.t1_id) AS t3 ORDER BY t3.t1_id ASC,
>>t3.t2_name DESC, t3.t1_name ASC' {}
>>
>>
>>and here is the error:
>>
>> raise exceptions.SQLError(statement, parameters, e)
>>sqlalchemy.exceptions.SQLError: (ProgrammingError) ERROR: column
>>"t3.t1_id" must appear in the GROUP BY clause or be used in an
>>aggregate function
>>
>>Sorry that the test case is more complicated than necessary. I was
>>duplicating my code trying to find the error's cause.
>>
>>Randall
>>"""Delete fails after access to a lazy attribute.
>>
>>"""
>>from sqlalchemy import *
>>from sqlalchemy.ext.selectresults import SelectResults
>>
>>metadata = DynamicMetaData(name="test")
>>
>>t1 = Table('t1', metadata,
>> Column('id', Integer, primary_key=True),
>> Column('name', String)
>> )
>>
>>t2 = Table('t2', metadata,
>> Column('id', Integer, primary_key=True),
>> Column('t1_id', Integer, ForeignKey(t1.c.id)),
>> Column('name', String)
>> )
>>
>>t3 = select([t1.c.id.label('t1_id'), t1.c.name.label('t1_name'),
>> t2.c.id.label('t2_id'), t2.c.id.label('t2_name'),
>> ],
>> t1.c.id == t2.c.t1_id
>>).alias('t3')
>>
>>class T1(object):
>> pass
>>
>>class T2(object):
>> pass
>>
>>class T3(object):
>> pass
>>
>>mapper(T1, t1,
>>)
>>mapper(T2, t2,
>> properties={'t1':relation(T1, backref=backref('t2s',
>>private=True))}
>>)
>>mapper(T3, t3)
>>
>>engine = create_engine("sqlite:///:memory:")
>>engine = create_engine("postgres:///test")
>>engine.echo = True
>>metadata.connect(engine)
>>metadata.create_all()
>>
>># Populate tables.
>>session = create_session(bind_to=engine)
>>for i in range(10):
>> t1obj = T1()
>> t1obj.name = 'test'
>> session.save(t1obj)
>> for i in range(5):
>> t2obj = T2()
>> t2obj.name = 'test'
>> t1obj.t2s.append(t2obj)
>>session.flush()
>>session.close()
>>
>># SelectResults
>>session = create_session(bind_to=engine)
>>query = session.query(T3)
>>sr = SelectResults(query)
>>ordering = [asc(T3.c.t1_id), desc(T3.c.t2_id), asc(T3.c.t1_name)]
>>sr = sr.order_by(ordering)
>>print sr.max(T3.c.t1_id)
>>session.flush()
>>session.close()
>>----------------------------------------------------------------------
>>---
>>Take Surveys. Earn Cash. Influence the Future of IT
>>Join SourceForge.net's Techsay panel and you'll get the chance to
>>share your
>>opinions on IT & business topics through brief surveys -- and earn
>>cash
>>http://www.techsay.com/default.php?
>>page=join.php&p=sourceforge&CID=DEVDEV________________________________
>>_______________
>>Sqlalchemy-users mailing list
>>Sqlalchemy-users@lists.sourceforge.net
>>https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
>
>
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys -- and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
