you need to use an outer join (or a subselect) to get a row to come back even when there is no match in the right table.
adding from_obj=[outerjoin(product_table, stock_product_table)] to what you have now would probably work. (personally i think a subselect would be better style here but i can't predict if it would perform better. since you are so close to having it working the other way maybe you should just run with that. :) On 2/25/07, Koen Bok <[EMAIL PROTECTED]> wrote: > > 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 -~----------~----~----~----~------~----~------~--~---