I want to create a view of Magento products. The following query will do that:
Note: Magento's default records used to create it.
SELECT
P.entity_id as uid,
P.created_at as cdate, # Created to updated date?
PN.value as name,
C.attribute_set_name as category,
PR.value as price,
'INR' as price_unit,
concat('http://www.mydemo.com/media/catalog/product/', U.value) as pic_url,
D.value as description,
concat('http://www.mydemo.com/', PU.value) as product_url
FROM catalog_product_entity as P
LEFT OUTER JOIN catalog_product_entity_varchar as PN
ON P.entity_id = PN.entity_id
AND PN.attribute_id = 71 # 71 Product name
LEFT OUTER JOIN eav_attribute_set C
ON C.attribute_set_id = P.attribute_set_id
LEFT OUTER JOIN catalog_product_entity_decimal PR
ON PR.entity_id = P.entity_id
AND PR.attribute_id = 75 # Price
LEFT OUTER JOIN catalog_product_entity_media_gallery as U
ON P.entity_id = U.entity_id
JOIN catalog_product_entity_media_gallery_value as V
ON U.value_id = V.value_id
AND V.position = 2
LEFT OUTER JOIN catalog_product_entity_text as D
ON P.entity_id = D.entity_id
AND D.attribute_id = 72
LEFT OUTER JOIN catalog_product_entity_varchar as PU
ON P.entity_id = PU.entity_id
AND PU.attribute_id = 98
AND PU.store_id = 1
WHERE P.entity_id in ('402', '403', '404', '413')
ORDER BY P.entity_id
Here, it will show the products with its price. For the product id 403 it shows the price as 175.00 but it has a special price 140. What I want to show is if special price is there it show that otherwise its actual price.
Currently:
uid price
402 190.00
403 175.00 # this is wrong because it has spacial price is 140
404 160.00
413 125.00
Expected result:
uid price
402 190.00
403 140.00
404 160.00
413 125.00
If I pass PR.attribute_id = 75, it gives price if PR.attribute_id = 76 it gives special price. How can we show both values in the same result. How should I modify the query to achieve this?
UPDATE:
The query is updated as below to get the value as expected:
SELECT
#CP.special_from_date,
#CP.special_to_date,
P.entity_id as uid,
P.created_at as cdate, # Created to updated date?
PN.value as name,
C.attribute_set_name as category,
#PR.value as price,
#PRN.value as normal_price,
#PRS.value as special_price,
coalesce(PRS.value, PRN.value) as price,
'INR' as price_unit,
concat('http://www.mydemo.com/media/catalog/product/', U.value) as pic_url,
D.value as description,
concat('http://www.mydemo.com/', PU.value) as product_url
FROM catalog_product_entity as P
LEFT OUTER JOIN catalog_product_entity_varchar as PN
ON P.entity_id = PN.entity_id
AND PN.attribute_id = 71 # 71 Product name
LEFT OUTER JOIN eav_attribute_set C
ON C.attribute_set_id = P.attribute_set_id
INNER JOIN catalog_product_entity_decimal PRN
ON PRN.entity_id = P.entity_id
AND PRN.attribute_id = 75 # Price
LEFT JOIN catalog_product_entity_decimal PRS
ON PRS.entity_id = P.entity_id
AND PRS.attribute_id = 76 # Special Price
LEFT JOIN catalog_product_flat_1 as CP
ON CP.entity_id = P.entity_id
AND CURRENT_DATE() BETWEEN CP.special_from_date and CP.special_to_date
LEFT OUTER JOIN catalog_product_entity_media_gallery as U
ON P.entity_id = U.entity_id
JOIN catalog_product_entity_media_gallery_value as V
ON U.value_id = V.value_id
AND V.position = 2
LEFT OUTER JOIN catalog_product_entity_text as D
ON P.entity_id = D.entity_id
AND D.attribute_id = 72
LEFT OUTER JOIN catalog_product_entity_varchar as PU
ON P.entity_id = PU.entity_id
AND PU.attribute_id = 98
AND PU.store_id = 1
Another thread which helped me to fix it: https://stackoverflow.com/questions/31770234/how-the-write-the-query-to-get-the-special-price-if-available
