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