clearly "correlate=False" has to be more prominently mentioned, not just in the FAQ but in the main docs, there should be a section specifically on subqueries and their mechanics.
On Mar 22, 2007, at 6:53 AM, King Simon-NFHD78 wrote: > > 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 -~----------~----~----~----~------~----~------~--~---
