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