0

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

  • As for me, the question is not clear. As far as I can see, you'd like to create your own listing with many-rows query. And you want to find someone who fix your query? Why don't you create a category, assign necessary products there and show this category anywhere you like? – zhartaunik Jul 31 '15 at 13:35
  • @zhartaunik, What you "see" is wrong!, for my project I'm collecting products from different e-commerce sites, one of the site is in Magento. So from Magento database I want to fetch the product details including its price. The above query written just after install Magento in my local PC with default set of products which Magento provided. The "help" I'm expecting is to update the query which can get the price properly. It should take the special price for all the products. if special price is not available it should take normal price(at is fetchs now). – San Jul 31 '15 at 14:02
  • If you have default Magento installation you can execute the above query. I know this query is more related to MySQL but the guys in Magento can understand the things more (I guess) – San Jul 31 '15 at 14:24
  • So, if I understand this question correctly, you don't want (or can) use magento collections for this, as you intend to whack the db directly from an external application? – ProxiBlue Jan 24 '16 at 0:10
  • Also, have you considered that products prices can also be affected by catalog rules, not just special price attribute values? – ProxiBlue Jan 24 '16 at 0:11
0

You could join the query to the decimal table twice, picking up price once and special_price the second time, then use an if statement in the SELECT part of the query to return the right one. The answer you get may still be wrong though, as there's another two fields, special_to_date and special_from_date that you'll have to check to make sure the special price is valid.

The "proper" (more accurate but slower) way of doing your query there would be to use a ProductCollection to query for what you want at let Magento worry about how it's data structures work.

  • thank you very much for your suggestion, I will try this and let you know – San Aug 1 '15 at 10:18
0

For Magento 1.9. Here 76 is attribute_id for special_price attribute.

select cpe.sku,cpe.type_id, cped.store_id, cped.value from catalog_product_entity_decimal cped
RIGHT OUTER JOIN catalog_product_entity cpe on cpe.entity_id = cped.entity_id 
where cped.attribute_id=76
order by cpe.type_id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.