Hello again!
I need a mapper that does an outer join on another table.
stock_request_join = sql.outerjoin(request_table, stock_table, 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))
stock_request = select(
[stock_request_join] + \
[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')],
group_by=[c for c in stock_request_join.c],
correlate=False).alias('stock_request')
This generates the right SQL. But I can't get it to work with the
original column names, then I get name ambigious errors. When I do it
with the joined names, the mapper tries to insert a request too when I
want to create stock which makes kind of sense.
When I remove [stock_request_join] and insert [c for c in
stock_request_join.c] the JOIN syntax is being removed and it does a
regular select against request and stock.
Basically I want to end up with a regular Stock object with the
aggregrate columns request_table.c.quantity and
request_table.c.allocation. I have it working with a regular select,
but if there is no request in te table linked to a stock it does not
return that stock when doing a session.query(Stock).select(). Left
outer joining request would do the trick.
Thanks in advance!
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
-~----------~----~----~----~------~----~------~--~---
