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