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_entitytable withcatalog_product_entity_decimaltwiceâ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!