the count(stockproducts.c.id) is internal to the subquery that you are mapping to, and its an aggregate for how many rows in stockproducts match each product id. it cant be changed by external criterion. the easiest query to map to for this is:
select products.id, products.name, count(stockproducts.stock_id) from products, stockproducts where products.id=stockproducts.product_id group by products.id, products.name the other query you want is completely different: select products.id, products.name, count(stockproducts.stock_id) from products, stockproducts where products.id=stockproducts.product_id and stockproducts.stock_id=1 group by products.id, products.name the "stock_id" qualifier has to be internal to the query for it to be part of the aggregate function. id just use straight SQL for this kind of thing; mappers were designed to map against fully contained views or tables, i.e. the data contained within each row is never going to change based on outside criterion; only the total rows you get back. On Jun 12, 2006, at 4:28 AM, Koen Bok wrote: > Hi all, > > I have three tables. Products, StockProducts and Stocks. For each > product stock a stock product is created. To get the total stock > for a product we do a count() for the related stockproducts. I got > this to work with a mapper object. Now I'd like to filter these by > a certain stock, but I can't get it to work. I do a select_by on > the mapper, but it keeps returning the total stockproducts. > > I included a fully working test script which exactly describes my > problem. What am I doing wrong!? > > Thanks! > Koen > > <test_stocks.py> > _______________________________________________ > Sqlalchemy-users mailing list > Sqlalchemy-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users _______________________________________________ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
