Paulo Aquino wrote: > On Tue, Sep 15, 2009 at 5:53 AM, Michael Bayer > <mike...@zzzcomputing.com>wrote: > >> >> Paulo Aquino wrote: >> > I have 2 tables 'Product' and 'ProductPrice'. I want to get all valid >> > products, a product is valid if it has both a valid 'Selling' and >> 'Buying' >> > ProductPrice type. A ProductPrice is valid if the valid_from date <= >> > date.today() and valid_to >= date.today(). >> > >> > Product Table: >> > >> > id | sku | principal >> > ----+-------+----------- >> > 1 | sku_1 | kraft >> > 2 | sku_2 | kraft >> > 3 | sku_3 | kraft >> > 4 | sku_4 | kraft >> > >> > ProdutPrice Table: >> > >> > id | type | sku | principal | price | valid_from | valid_to >> > >> ----+-----------+---------+-------------+--------+-----------------+------------ >> > 1 | Buying | sku_1 | kraft | 122 | 2009-05-05 | 2009-12-12 >> > 2 | Buying | sku_1 | kraft | 231 | 2010-01-01 | 2010-02-02 >> > 3 | Selling | sku_1 | kraft | 125 | 2009-05-05 | 2009-12-12 >> > 4 | Buying | sku_2 | kraft | 122 | 2009-05-05 | 2009-12-12 >> > 5 | Buying | sku_2 | kraft | 231 | 2010-01-01 | 2010-02-02 >> > 6 | Selling | sku_2 | kraft | 125 | 2009-05-05 | 2009-12-12 >> > 7 | Buying | sku_3 | kraft | 122 | 2009-05-05 | 2009-12-12 >> > 8 | Selling | sku_4 | kraft | 122 | 2009-05-05 | 2009-12-12 >> > >> > Using Raw SQL here is how I did it: >> > >> > 1. SELECT product.id, >> > type, >> > product.sku, >> > product.principal, >> > price, >> > valid_from, >> > valid_to INTO TEMP >> > FROM product >> > INNER JOIN product_price on >> > product.principal = product_price.principal AND >> > product.sku = product_price.sku >> > WHERE valid_from <= current_date AND valid_to >= current_date ; >> > >> > 2. SELECT DISTINCT * from TEMP a , TEMP b >> > WHERE a.type='Selling' AND b.type='Buying' >> > AND a.principal = b.principal >> > AND a.sku = b.sku; >> > >> > From this two queries I now have distinct products that have a valid >> pair >> > of >> > both 'Buying' and 'Selling' Price. (Those products with a valid >> 'Buying' >> > or >> > 'Selling price only and not have them both are dropped) >> > >> > >> > Using SQLAlchemy here is how I did it: >> > >> > 1. valid_price = and_(ProductPrice.sku == Product.sku, >> > ProductPrice.principal==Product.principal, >> > ProductPrice.valid_from <= >> date.today(), >> > ProductPrice.valid_to >= date.today()) >> > >> > valid_products = >> > session.query(Product).join(ProductPrice).filter(valid_price) >> > >> > 2. Now I want to self join valid_products, same thing I did in my Raw >> SQL >> > solution no. 2 I've been trying but getting weird results. >> > >> > >> > If someone can please help me, here's my test case >> > http://pastebin.com/m3f8a95c8 >> >> you can say: >> >> valid_products = session.query(Product, >> ProductPrice.type).join(ProductPrice).filter(valid_price) >> >> a = valid_products.subquery() >> b = valid_products.subquery() >> >> PA = aliased(Product, a) >> PB = aliased(Product, b) >> >> q = session.query(PA, PB).\ >> distinct().\ >> filter(a.c.type=='Selling').\ >> filter(b.c.type=='Buying').\ >> filter(a.c.principal==b.c.principal).\ >> filter(a.c.sku==b.c.sku) >> > > This works perfectly fine, but count() is wrong, seems to return a > count > which is more than 1 from what is expected.
dont use query.count() for queries like these. use query.value(func.count(<somecolumn>)). --~--~---------~--~----~------------~-------~--~----~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
