This caught me out a couple of weeks ago, and I've seen a couple of other similar questions as well. You need to add 'correlate=False' to the nested select.
I wonder if this should be added to the FAQ? Hope that helps, Simon -----Original Message----- From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Koen Bok Sent: 22 March 2007 10:47 To: sqlalchemy Subject: [sqlalchemy] Re: Using mapper with custom select creates unneeded subquery Let me post some sample code with that: mapper(Request, request_table, properties={ 'children' : relation( Request, primaryjoin=request_table.c.id_parent==request_table.c.id, backref=backref("parent", remote_side=[request_table.c.id])), 'i': relation(Item, primaryjoin=item_table.c.id==request_table.c.id_item, backref='requests', lazy=True), [SOME MORE STUFF] 'stock': relation(Stock, primaryjoin=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), foreign_keys=[stock_table.c.id_product, stock_table.c.id_location, stock_table.c.id_stocktype])}) stock_request = select( [c for c in stock_table.c] + \ [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_request') mapper(Stock, stock_request, 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)}) If you need more, just let me know! Koen On Mar 22, 11:42 am, "Koen Bok" <[EMAIL PROTECTED]> 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 sqlalchemy@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---
