Get the special price of the product using query in Magento
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom: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
magento-1.9 database special-price
edited Nov 11 '18 at 19:08
Shashank Kumrawat
1,3161343
asked Jul 31 '15 at 13:14
SanSan
10613
bumped to the homepage by Community♦ 9 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
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
magento-1.9 database special-price
edited Nov 11 '18 at 19:08
Shashank Kumrawat
1,3161343
asked Jul 31 '15 at 13:14
SanSan
10613
bumped to the homepage by Community♦ 9 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
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
add a comment |
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
magento-1.9 database special-price
edited Nov 11 '18 at 19:08
Shashank Kumrawat
1,3161343
asked Jul 31 '15 at 13:14
SanSan
10613
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
magento-1.9 database special-price
magento-1.9 database special-price
edited Nov 11 '18 at 19:08
Shashank Kumrawat
1,3161343
asked Jul 31 '15 at 13:14
SanSan
10613
edited Nov 11 '18 at 19:08
Shashank Kumrawat
1,3161343
asked Jul 31 '15 at 13:14
SanSan
10613
edited Nov 11 '18 at 19:08
Shashank Kumrawat
1,3161343
edited Nov 11 '18 at 19:08
Shashank Kumrawat
1,3161343
edited Nov 11 '18 at 19:08
Shashank Kumrawat
1,3161343
1,3161343
asked Jul 31 '15 at 13:14
SanSan
10613
asked Jul 31 '15 at 13:14
SanSan
10613
asked Jul 31 '15 at 13:14
SanSan
10613
10613
bumped to the homepage by Community♦ 9 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 9 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
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
add a comment |
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
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
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
@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
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
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
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
add a comment |
2 Answers
2
active
oldest
votes
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.
answered Jul 31 '15 at 14:50
RichardRichard
1,672717
thank you very much for your suggestion, I will try this and let you know
– San
Aug 1 '15 at 10:18
add a comment |
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
edited Jul 12 '16 at 19:55
7ochem
5,84493768
answered Jul 12 '16 at 16:52
Rajeev SinghRajeev Singh
1
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "479"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmagento.stackexchange.com%2fquestions%2f76276%2fget-the-special-price-of-the-product-using-query-in-magento%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Jul 31 '15 at 14:50
RichardRichard
1,672717
thank you very much for your suggestion, I will try this and let you know
– San
Aug 1 '15 at 10:18
add a comment |
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.
answered Jul 31 '15 at 14:50
RichardRichard
1,672717
thank you very much for your suggestion, I will try this and let you know
– San
Aug 1 '15 at 10:18
add a comment |
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.
answered Jul 31 '15 at 14:50
RichardRichard
1,672717
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.
answered Jul 31 '15 at 14:50
RichardRichard
1,672717
answered Jul 31 '15 at 14:50
RichardRichard
1,672717
answered Jul 31 '15 at 14:50
RichardRichard
1,672717
answered Jul 31 '15 at 14:50
RichardRichard
1,672717
1,672717
thank you very much for your suggestion, I will try this and let you know
– San
Aug 1 '15 at 10:18
add a comment |
thank you very much for your suggestion, I will try this and let you know
– San
Aug 1 '15 at 10:18
thank you very much for your suggestion, I will try this and let you know
– San
Aug 1 '15 at 10:18
thank you very much for your suggestion, I will try this and let you know
– San
Aug 1 '15 at 10:18
add a comment |
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
edited Jul 12 '16 at 19:55
7ochem
5,84493768
answered Jul 12 '16 at 16:52
Rajeev SinghRajeev Singh
1
add a comment |
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
edited Jul 12 '16 at 19:55
7ochem
5,84493768
answered Jul 12 '16 at 16:52
Rajeev SinghRajeev Singh
1
add a comment |
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
edited Jul 12 '16 at 19:55
7ochem
5,84493768
answered Jul 12 '16 at 16:52
Rajeev SinghRajeev Singh
1
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
edited Jul 12 '16 at 19:55
7ochem
5,84493768
answered Jul 12 '16 at 16:52
Rajeev SinghRajeev Singh
1
edited Jul 12 '16 at 19:55
7ochem
5,84493768
edited Jul 12 '16 at 19:55
7ochem
5,84493768
edited Jul 12 '16 at 19:55
7ochem
5,84493768
5,84493768
answered Jul 12 '16 at 16:52
Rajeev SinghRajeev Singh
1
answered Jul 12 '16 at 16:52
Rajeev SinghRajeev Singh
1
answered Jul 12 '16 at 16:52
Rajeev SinghRajeev Singh
1
1
add a comment |
add a comment |
Thanks for contributing an answer to Magento Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmagento.stackexchange.com%2fquestions%2f76276%2fget-the-special-price-of-the-product-using-query-in-magento%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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