Magento 2 - get special price of product by product id or sku

Question

I need to get the special price of a product by product Id or Sku. Let's say I have an existing product with Id '21' and Sku 'test-21'.

This information is provided in my custom block class, how can I get the special price of this product in mentioned custom class?

Answer 1

use Magento/Catalog/Model/ProductFactory to load product, and get price information from the model

Code is like:

$specialPrice = $this->productFactory()->create()->load($id)->getPriceInfo()->getPrice('special_price');

some reference about how to get product model in your custom module

use object manager: http://alanstorm.com/magento_2_object_manager_instance_objects/

use dependency injection: http://www.coolryan.com/magento/2016/01/27/dependency-injection-in-magento-2/

Normally dependency injection is recommended.

Answer 2

Use \Magento\Catalog\Model\ProductRepository class to get the special price of the product

protected $_productRepository;

public function __construct(
    ...      
    \Magento\Catalog\Model\ProductRepository $productRepository,
    ...
) {
    $this->_productRepository = $productRepository;
}

public function getSpecialPriceById($id)
{
    //$id = '21'; //Product ID
    $product = $this->_productRepository->getById($id);
    return $product->getSpecialPrice();
}

public function getSpecialPriceBySku($sku)
{   
    //$sku = 'test-21'; //Product Sku
    $product = $this->_productRepository->get($sku);
    return $product->getSpecialPrice();
}

Now you can get special price by

$id = '21';
$sku = 'test-21';
$this->getSpecialPriceById($id);
$this->getSpecialPriceBySku($sku);

Answer 3

Here I am sharing the script to get the configurable product with special price.

magento2 SQL Query get special price for the configurable product with associated products:-

SELECT e.sku,e.type_id, d1.value as price, d2.value as special_price from catalog_product_entity e LEFT JOIN catalog_product_entity_decimal d1 ON e.entity_id = d1.entity_id
AND d1.store_id = 0
AND d1.attribute_id =75
LEFT JOIN catalog_product_entity_decimal d2 ON e.entity_id = d2.entity_id
AND d2.store_id = 0
AND d2.attribute_id =76

Answer 4

May be there is a better way, for me this worked in (Magento 2.3)

$_product = $this->productloader->create()->load($productId);
$tierPrice = $_product->getTierPrice();

it returns

"tier_price":
    [
        {
            "price_id":"1",
            "website_id":"0",
            "all_groups":"0",
            "cust_group":"0",
            "price":85,
            "price_qty":"1.0000",
            "percentage_value":"15.00",
            "website_price":85
        },{
            "price_id":"2",
            "website_id":"0",
            "all_groups":"0",
            "cust_group":"1",
            "price":85,
            "price_qty":"1.0000",
            "percentage_value":"15.00",
            "website_price":85
        }
    ]

Settings in Magento Advanced Pricing:

Answer 5

NOTE: Not an answer to OP's question but based on Saurabh's SQL query, I came with modified query that would give me all active configurables with special price. This would be helpful to export current products with marked down prices and adjust if required.

Also, I'm selecting 'M-%' SKUs only as required currently.

SELECT 
  e.sku,e.type_id, priceTable.value AS price, specialPriceTable.value AS special_price, statusTable.value AS `status`
FROM 
  catalog_product_entity AS e 
LEFT JOIN 
  catalog_product_entity_decimal AS priceTable
ON 
  e.entity_id = priceTable.row_id
LEFT JOIN 
  catalog_product_entity_decimal AS specialPriceTable 
ON 
  e.entity_id = specialPriceTable.row_id
LEFT JOIN 
  catalog_product_entity_int AS statusTable
ON 
  e.row_id=statusTable.row_id
WHERE 
  e.type_id='configurable' AND e.sku like 'M-%' 
  AND statusTable.attribute_id=96 AND statusTable.store_id=0 AND statusTable.value =1
  AND priceTable.attribute_id =75 AND priceTable.store_id = 0
  AND specialPriceTable.attribute_id =76 AND specialPriceTable.store_id = 0 AND specialPriceTable.value is not null 
  AND priceTable.value > specialPriceTable.value

Answer 6

Please try the following query to get the price and special price of products by SKU:

SELECT cpe.sku,cpe.type_id, p1.value as price, p2.value as special_price from catalog_product_entity cpe 
LEFT JOIN catalog_product_entity_decimal p1 ON cpe.row_id = p1.row_id AND p1.store_id = 0 AND p1.attribute_id = (select attribute_id from eav_attribute where entity_type_id = 4 and attribute_code='price') 
LEFT JOIN catalog_product_entity_decimal p2 ON cpe.row_id = p2.row_id AND p2.store_id = 0 AND p2.attribute_id = (select attribute_id from eav_attribute where entity_type_id = 4 and attribute_code='special_price') 
LEFT JOIN catalog_product_website cw ON cpe.entity_id = cw.product_id where cpe.sku in (sku1,sku2,sku3) and cw.website_id = 9;

Note: Please Update the SKU list and website_id (you can use sub-query to pull the website_id) in the query.