in not really sure if the aggregate functions in SelectResults should
convert everything to a subquery first, then call the aggrgate on
that; SelectResults is based on people's desire for a particular
functionality they missed from SQLObject (which im not terribly
familiar with), and it was contributed code; as far as I know it
works very similarly to SQLObject.
such a feature would require a much more complicated SelectResults
implementation though, it would probably have to re-implement some of
the functionality within Query to re-create the full query as a
subquery and operate upon that. right now its just a very simple
wrapper and doesnt deal with the details of constructing the mapper's
queries. Also the aggregate functions take a Column object as the
argument, so that would have to be mapped out to the corresponding
Column on the subquery object for it to work (selectables do support
this operation).
On Jul 21, 2006, at 2:23 PM, Randall Smith wrote:
> 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
-------------------------------------------------------------------------
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
