-
-
Save tegansnyder/7828065 to your computer and use it in GitHub Desktop.
SELECT `e`.entity_id, `at_category_id`.`category_id` | |
FROM `catalog_product_entity` AS `e` | |
LEFT JOIN `catalog_category_product` AS `at_category_id` | |
ON (at_category_id.`product_id`=e.entity_id) |
How to get best selling products list based on category-wise?
ChatGTP:
To create an SQL query that retrieves all category names for a specific product entity_id
in a Magento shop database, you'll need to understand the database schema for Magento. Magento uses an EAV (Entity Attribute Value) model for storing data, which can make querying it directly a bit complex. However, for fetching category names for a specific product, we'll primarily be concerned with a few key tables:
catalog_category_entity_varchar
: This table stores category attributes, including the name of the category.catalog_category_product
: This table links products to categories.catalog_product_entity
: This table stores product information.
Given this, the SQL query to fetch all category names for a specific product entity_id
would involve joining these tables. The precise query might vary slightly depending on your Magento version (e.g., Magento 1 vs Magento 2), but the following is a general approach for Magento 2:
SELECT ccev.value AS category_name
FROM catalog_category_entity_varchar AS ccev
JOIN catalog_category_entity AS cce ON ccev.entity_id = cce.entity_id
JOIN catalog_category_product AS ccp ON ccp.category_id = cce.entity_id
JOIN catalog_product_entity AS cpe ON ccp.product_id = cpe.entity_id
WHERE cpe.entity_id = :productEntityId
AND ccev.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_category'))
AND ccev.store_id = 0;
In this query:
- Replace
:productEntityId
with the specific productentity_id
you're inquiring about. - This query assumes you're looking for category names in the default store view (
store_id = 0
). You might need to adjuststore_id
based on your setup. - The query dynamically retrieves the
attribute_id
for the category name, which allows it to be more adaptable to different Magento setups.
Remember, the exact structure of your Magento database might vary, especially if customizations or extensions are involved. It's a good idea to check your database schema to ensure these table and column names align with your setup. Also, running complex queries directly against your Magento database should be done with caution, especially on a live system, as it can impact performance.
Possible to query all in stock products by category id?