Dear all.
I have to make a complicated mapper, and I need a little help.
We have a list of products. These products have stock, each individual
stock item has an entry in the stockproduct table. So to get the total
stock we need to count the items in the stock database. We can filter
them by a particular stock.
So I made a function to create a mapper to do just that. But there are
two problems:
- It's not working well, because if the count function equals 0 (no
stock) the product does not appear in the results.
- I have the feeling this can be better optimized, but I can't see it
(maybe put it in a join or subquery?)
The function
def productStockMapper(stockList):
or_list = or_()
for stock in stockList:
or_list.append(stockproduct_table.c.stock_id==stock.id)
s = select([product_table,
func.count(stockproduct_table.c.id).label('stock')],
and_(
stockproduct_table.c.product_id==product_table.c.id,
or_list),
group_by=[c for c in product_table.c]).alias('count_select')
return mapper(Product, s, non_primary=True)
The tables:
product_table = Table('products', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(255), nullable=False)
Column('code', Unicode(255), unique=True, nullable=False))
stockproduct_table = Table('stockproducts', metadata,
Column('id', Integer, primary_key=True),
Column('stock_id', Integer, ForeignKey("stocks.id"), nullable=False),
Column('product_id', Integer, ForeignKey("products.id"),
nullable=False))
stock_table = Table('stocks', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(255), nullable=False))
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
