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