How to get special price magento 2

How to get special price magento 2

September 15, 2026 · Illustrated author avatarBy Stefan Bogdanescu
How to get special price magento 2

Adding Special Price to Magento Products via Catalog Product Entity

In Magento 2, you can easily add special prices to products using the catalog_product_entity_decimal table. The special price attribute has an ID of 78 in this table. To retrieve both the regular price (attribute_id 77) and the special price (attribute_id 78) via SQL joins, follow these steps:

Step 1: Understanding the Tables

Before starting, let’s understand the main tables involved:

  • catalog_product_entity_decimal: Stores product attributes of type decimal.
  • catalog_product_entity: Stores basic product information.

Step 2: Writing the Query

Here is a sample SQL query that retrieves both regular and special prices using joins:

SELECT
    p.entity_id,
    p.sku,
    p.name,
    pd.value as price,
    IFNULL(psd.value, 0) AS special_price
FROM
    catalog_product_entity AS p
LEFT JOIN
    catalog_product_entity_decimal AS pd ON (
        p.entity_id = pd.entity_id AND
        pd.attribute_id = 77 -- Regular price attribute
    )
LEFT JOIN
    catalog_product_entity_decimal AS psd ON (
        p.entity_id = psd.entity_id AND
        psd.attribute_id = 78 -- Special price attribute
    );

This query does the following:

  • Selects the product ID (entity_id), SKU, name, regular price, and special price.
  • Joins the catalog_product_entity table with catalog_product_entity_decimal twice—once for each attribute.

Step 3: Interpreting the Results

The results will be a list of all products along with their regular and special prices. If no special price is set for a product, the special_price column will return 0.

Best Practices for Adding Special Prices in Magento 2

While writing queries directly on tables works, consider these best practices:

Use Magento Repositories and Collections

For a more object-oriented approach, use Magento’s built-in repositories and collections. This ensures proper data validation and reduces the risk of SQL injection.

$productRepository = $objectManager->get('Magento\Catalog\Model\ProductRepository');
$products = $productRepository->getList();

Utilize Magento’s Price Indexing

Magento maintains price indexes to optimize performance when dealing with pricing rules. Ensure you refresh these indexes after making changes:

php bin/magento indexer:reindex catalog_product_index_price

Conclusion

Adding special prices in Magento 2 involves working with the catalog_product_entity_decimal table, specifically using attribute IDs 77 for regular price and 78 for special price. Remember to use proper joins to retrieve both attributes.

Always consider Magento’s built-in object-oriented approaches rather than direct SQL queries for better data management and security. Furthermore, ensure you refresh the price indexes after making changes to keep your store optimized.

For more advanced price customizations and promotions in Magento 2, I recommend exploring the Laravel Company blog for detailed tutorials and best practices in e-commerce development.

Happy coding!

Magento 2 Catalog Database Pricing

Our network