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