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 is my test case
http://pastebin.com/m3f8a95c8
rgds,
Paulo
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
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
-~----------~----~----~----~------~----~------~--~---
