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