put "correlate=False" on your subquery.
On Mar 22, 2007, at 6:42 AM, Koen Bok wrote:
>
> Thanks for the reply! If the performance is about equal, that's fine!
>
> But I think I might have found a bug.
>
> When I make a selection it generates the following (faulty) SQL query:
>
> SELECT
> stock_request.id_stocktype AS stock_request_id_stocktype,
> stock_request.unordered AS stock_request_unordered,
> stock_request.id_location AS stock_request_id_location,
> stock_request.id_product AS stock_request_id_product,
> stock_request.unallocated AS stock_request_unallocated,
> stock_request.quantity AS stock_request_quantity,
> stock_request.id AS stock_request_id
> FROM
> (
> SELECT
> stock.id AS id,
> stock.id_stocktype AS id_stocktype,
> stock.id_product AS id_product,
> stock.id_location AS id_location,
> stock.quantity AS quantity,
> (stock.quantity - sum(request.quantity)) AS unordered,
> (stock.quantity - sum(request.allocation)) AS unallocated
> FROM request
> WHERE
> request.id_item = stock.id_product
> AND
> request.id_location = stock.id_location
> AND
> request.id_stocktype = stock.id_stocktype
> GROUP BY
> stock.id,
> stock.id_stocktype,
> stock.id_product,
> stock.id_location,
> stock.quantity,
> stock.quantity
> ) AS stock_request, stock
> WHERE
> stock.id_product = 5
> AND
> stock.id_location = 7
> AND
> stock.id_stocktype = 1
> ORDER BY
> stock_request.id
> LIMIT 1
>
> The FROM in the subquery should be: FROM request, stock
>
> The strange thing is that whenever I print the subquery's sql, it has
> stock in the FROM and tehrefore is correct.
>
> Or am I not understanding it right?
>
> Koen
>
> On Mar 22, 2:58 am, Michael Bayer <[EMAIL PROTECTED]> wrote:
>> when you pass a selectable to the mapper, the mapper considers that
>> selectable to be encapsulated, in the same way as a table is. the
>> Query cannot add any extra criterion to that selectable directly
>> since it would modify the results and corrupt the meaning, if not the
>> actual syntax, of the selectable itself. therefore the mapper is
>> always going to select * from (your selectable) - its the only way to
>> guarantee the correct results.
>>
>> the queries it generates, i.e. select * from (select * from ...))
>> will be optimized by the database's optimizer in most cases and
>> should not add any overhead to your application.
>>
>> On Mar 21, 2007, at 8:08 PM, Koen Bok wrote:
>>
>>
>>
>>> My mapper looks like this:
>>
>>> stock_unreserved = select(
>>> [stock_table] + \
>>> [stock_table.c.quantity.op('-')
>>> (func.sum(request_table.c.quantity)).label('unordered')] + \
>>> [stock_table.c.quantity.op('-')
>>> (func.sum(request_table.c.allocation)).label('unallocated')],
>>> and_(
>>> request_table.c.id_item==stock_table.c.id_product,
>>> request_table.c.id_location==stock_table.c.id_location,
>>>
>>> request_table.c.id_stocktype==stock_table.c.id_stocktype),
>>> group_by=[c for c in stock_table.c]).alias('stock_unreserved')
>>
>>> mapper(Stock, stock_unreserved, properties={
>>> 'product': relation(Item,
>>> primaryjoin=item_table.c.id==stock_table.c.id_product,
>>> backref='_stock'),
>>> 'location': relation(Item,
>>> primaryjoin=item_table.c.id==stock_table.c.id_location),
>>> 'stocktype': relation(StockType)})
>>
>>> Whenever I try to select an object through the mapper I would
>>> think it
>>> would use the SQL from stock_unreserved which is:
>>
>>> SELECT
>>> stock.id,
>>> stock.id_stocktype,
>>> stock.id_product,
>>> stock.id_location,
>>> stock.quantity, (stock.quantity - sum(request.quantity)) AS
>>> unordered,
>>> (stock.quantity - sum(request.allocation)) AS unallocated
>>> FROM stock, request
>>> WHERE request.id_item = stock.id_product
>>> AND request.id_location = stock.id_location
>>> AND request.id_stocktype = stock.id_stocktype
>>> GROUP BY stock.id, stock.id_stocktype, stock.id_product,
>>> stock.id_location, stock.quantity
>>
>>> Selecting all objects by a plain select() on the mapper works great!
>>> But when I make a selection it does a subquery on all the results
>>> eg:
>>
>>> SELECT * FROM (SELECT * FROM stock_unreserved) WHERE selection
>>> criteria
>>
>>> But I want it to append it to the other selection criteria without
>>> doing a subselect eg:
>>
>>> SELECT * FROM stock_unreserved WERE ... AND ... + extra selection
>>> criteria
>>
>>> Is this possible at all?
>>
>>> Many thanks!
>>
>>> Koen
>
>
> >
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
