It worked! Thanks a bunch! On Mar 22, 5:06 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > 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 -~----------~----~----~----~------~----~------~--~---
