Alex Hellier
Reputation: 485
Display joined data with Peewee ORM
Using PeeWee Orm I have created two tables (I've removed a lot of the fields that are irrelevant to this query)
Product Table
class Product(BaseModel):
fm_price = FloatField(null=True)
magento = IntegerField(db_column='magento_id', null=True, index=True)
sku = CharField(index=True, null=True, unique=True)
# Compare these two fields to on or offline products
magento_status_fm = IntegerField(null=True)
magento_status_ff = IntegerField(null=True)
# Date fields to put error dates in
ff_price_error = DateField(index=True, null=True)
ff_stock_error = DateField(index=True, null=True)
fm_price_error = DateField(index=True, null=True)
fm_stock_error = DateField(index=True, null=True)
ff_check_date = DateField(null=True, index=True) # When did we get this product from FF
class Meta:
db_table = 'products'
indexes = (
(('fm_price', 'vip_price', 'education_price'), False),
)
Prices Table
class Price(BaseModel):
price_pk = PrimaryKeyField()
entity_id = ForeignKeyField(Product, to_field='magento', null=True, on_update='CASCADE', on_delete='CASCADE')
customer_group_id = IntegerField(null=True)
final_price = FloatField(null=True)
min_price = FloatField(null=True)
check_date = DateField(null=True)
class Meta:
indexes = (
(('entity_id', 'customer_group_id'), True),
)
Example Content:
Products:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------
| id | CREATION | MODIFICATION | fm_price | fm_stock | vip_price | education_price | magento_id | omit | sku | url | web_stock | magento_status_fm | magento_status_ff | ff_price_error | ff_stock_error | fm_price_error | fm_stock_error | fm_vip_price_error | ff_vip_price_error | ff_check_date |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------
| 3 | 2017-12-12 12:53:01 | 2017-12-14 09:31:44 | 109 | NULL | NULL | NULL | 15212 | NULL | epiea10nach1 | https://www.absolut… | NULL | NULL | 1 | NULL | NULL | NULL | NULL | NULL | NULL | 2017-12-14 |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------
| 5 | 2017-12-12 12:53:01 | 2017-12-14 09:31:44 | 545 | NULL | NULL | NULL | 15433 | NULL | yamapx700mk2blk | https://www.absolut… | NULL | NULL | 1 | NULL | NULL | NULL | NULL | NULL | NULL | 2017-12-14 |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------
Prices:
---------------------------------------------------------------------------------------------------------------------------
| price_pk | CREATION | MODIFICATION | entity_id_id | customer_group_id | final_price | min_price | check_date |
---------------------------------------------------------------------------------------------------------------------------
| 260 | 2017-12-12 12:53:39 | NULL | 15212 | 0 | 109 | 109 | NULL |
---------------------------------------------------------------------------------------------------------------------------
| 261 | 2017-12-12 12:53:39 | NULL | 15212 | 1 | 109 | 109 | NULL |
---------------------------------------------------------------------------------------------------------------------------
| 262 | 2017-12-12 12:53:39 | NULL | 15212 | 2 | 109 | 109 | NULL |
---------------------------------------------------------------------------------------------------------------------------
| 263 | 2017-12-12 12:53:39 | NULL | 15212 | 3 | 109 | 109 | NULL |
---------------------------------------------------------------------------------------------------------------------------
| 264 | 2017-12-12 12:53:39 | NULL | 15212 | 4 | 109 | 109 | NULL |
---------------------------------------------------------------------------------------------------------------------------
| 265 | 2017-12-12 12:53:39 | NULL | 15212 | 5 | 109 | 109 | NULL |
---------------------------------------------------------------------------------------------------------------------------
| 266 | 2017-12-12 12:53:39 | NULL | 15212 | 6 | 109 | 109 | NULL |
---------------------------------------------------------------------------------------------------------------------------
| 988 | 2017-12-12 12:53:42 | NULL | 15431 | 0 | 545 | 545 | NULL |
---------------------------------------------------------------------------------------------------------------------------
| 989 | 2017-12-12 12:53:42 | NULL | 15431 | 1 | 545 | 545 | NULL |
---------------------------------------------------------------------------------------------------------------------------
| 990 | 2017-12-12 12:53:42 | NULL | 15431 | 2 | 545 | 545 | NULL |
---------------------------------------------------------------------------------------------------------------------------
| 991 | 2017-12-12 12:53:42 | NULL | 15431 | 3 | 545 | 545 | NULL |
---------------------------------------------------------------------------------------------------------------------------
| 992 | 2017-12-12 12:53:42 | NULL | 15431 | 4 | 545 | 545 | NULL |
---------------------------------------------------------------------------------------------------------------------------
| 993 | 2017-12-12 12:53:42 | NULL | 15431 | 5 | 545 | 545 | NULL |
---------------------------------------------------------------------------------------------------------------------------
| 994 | 2017-12-12 12:53:42 | NULL | 15431 | 6 | 545 | 545 | NULL |
---------------------------------------------------------------------------------------------------------------------------
| 1002 | 2017-12-12 12:53:42 | NULL | 15433 | 0 | 545 | 545 | NULL |
---------------------------------------------------------------------------------------------------------------------------
| 1003 | 2017-12-12 12:53:42 | NULL | 15433 | 1 | 545 | 545 | NULL |
---------------------------------------------------------------------------------------------------------------------------
| 1004 | 2017-12-12 12:53:42 | NULL | 15433 | 2 | 545 | 545 | NULL |
---------------------------------------------------------------------------------------------------------------------------
| 1005 | 2017-12-12 12:53:42 | NULL | 15433 | 3 | 545 | 545 | NULL |
---------------------------------------------------------------------------------------------------------------------------
| 1006 | 2017-12-12 12:53:42 | NULL | 15433 | 4 | 545 | 545 | NULL |
---------------------------------------------------------------------------------------------------------------------------
| 1007 | 2017-12-12 12:53:42 | NULL | 15433 | 5 | 545 | 545 | NULL |
---------------------------------------------------------------------------------------------------------------------------
| 1008 | 2017-12-12 12:53:42 | NULL | 15433 | 6 | 545 | 545 | NULL |
---------------------------------------------------------------------------------------------------------------------------
These are joined together on the Magento_Id (magento) field which is an integer. The Product table has our correct price in The Prices table has the magento prices on the site for different customer groups
There can be many prices per product, and I wish to check that the prices are correct against our internal db (so the website is correct)
I have written a SQL query that shows me where the price in the prices table does not equal the price in the Product table.
I have also got this working in PeeWee Orm.
I am struggling however, in getting peewee to show me the final_price from the Price table, even though my SQL query uses it as a comparator.
Example SQL:
SELECT
sku, magento_id, fm_price, price.final_price, `url`
FROM
products
JOIN
price
ON
`products`.`magento_id` = `price`.`entity_id_id` AND `price`.`customer_group_id` = 0
WHERE
`products`.`fm_price` != `price`.`final_price`
Example output:
---------------------------------------------------------------------------------------------------------------
| sku | magento_id | fm_price | final_price | url |
---------------------------------------------------------------------------------------------------------------
| bsrhtstudio20h | 17581 | 399 | 449 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
---------------------------------------------------------------------------------------------------------------
| rolac60rw | 17697 | 479 | 475 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
---------------------------------------------------------------------------------------------------------------
| yamthr5 | 17833 | 169.85 | 169 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
---------------------------------------------------------------------------------------------------------------
| rolgk3b | 19636 | 155 | 152 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
---------------------------------------------------------------------------------------------------------------
| rolgk3 | 19763 | 155 | 152 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
---------------------------------------------------------------------------------------------------------------
| bosac3 | 20115 | 113 | 107.15 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
---------------------------------------------------------------------------------------------------------------
| bosbd2 | 20128 | 79 | 77.95 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
---------------------------------------------------------------------------------------------------------------
| bosch1 | 20140 | 77.5 | 71.92 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
---------------------------------------------------------------------------------------------------------------
And my Peewee query
price_errors = Product.select(Product, Price.final_price).join(Price, on=(Product.magento == Price.entity_id) & (
Price.customer_group_id == 0)).where(Product.fm_price != Price.final_price)
Which gives me the correct results, but I am unable to get access to the Price.final_price to display
I've tried the following:
for price_error in price_errors: print(price_error.price.final_price)
which gives:
AttributeError: 'Product' object has no attribute 'price'
I have access to price_set but that shows all the items related via the foreign key join, not the join specified above
Ive added alias's to both the join and the select, but am still unable to get anything.
If I execute it with
price_errors = Product.raw("""SELECT id, sku, magento_id, fm_price,
price.final_price, `url`
FROM products
JOIN price
ON `products`.`magento_id` =
`price`.`entity_id_id` AND `price`.`customer_group_id` = 0
WHERE `products`.`fm_price` !=
`price`.`final_price`""")
Then I can access price_error.final_price
but this kind of defeats the use of the orm :(
Upvotes: 0
Views: 113
Answers (1)
coleifer
Reputation: 26235
Try:
for product in price_errors:
print product.price.final_price
Upvotes: 0
Related Questions
- Peewee inner join returns None on the FK and does not include requested attributes
- Is it possible to make sql join on several fields using peewee python ORM?
- Include joined table columns in query dicts
- Join Queries in Peewee
- Translating SQL sub-query to Peewee ORM
- How to convert this query to the Peewee ORM?
- Using Peewee ORM to generate data for graphs
- Peewee getting column after join
- Python peewee joins multiple tables
- Python peewee - select from multiple tables