Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Query to retrieve all product ids and the category they belong to in Magento.
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)
@tegansnyder

This comment has been minimized.

Copy link
Owner Author

tegansnyder commented Dec 6, 2013

Find product that belong to one or more category

SELECT entity_id, GROUP_CONCAT(category_id) as category_ids FROM (
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)
) sub_query
GROUP BY entity_id
@tegansnyder

This comment has been minimized.

Copy link
Owner Author

tegansnyder commented Dec 6, 2013

Query products in multiple categories (each product has a default category, then the child cat)

SELECT entity_id, category_ids, (LENGTH(category_ids) - LENGTH(REPLACE(category_ids, ',', '')) + 1) as category_cnt FROM (
    SELECT entity_id, GROUP_CONCAT(category_id) as category_ids FROM (
        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)
    ) sub_query
    GROUP BY entity_id
) final_query
HAVING category_cnt > 2
@tegansnyder

This comment has been minimized.

Copy link
Owner Author

tegansnyder commented Dec 6, 2013

Query only products in one category:

SELECT entity_id, category_ids, (LENGTH(category_ids) - LENGTH(REPLACE(category_ids, ',', '')) + 1) as category_cnt FROM (
    SELECT entity_id, GROUP_CONCAT(category_id) as category_ids FROM (
        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)
    ) sub_query
    GROUP BY entity_id
) final_query
HAVING category_cnt = 2
@tegansnyder

This comment has been minimized.

Copy link
Owner Author

tegansnyder commented Dec 6, 2013

cleaner way to get product in only one category assuming default category is 2

SELECT entity_id, REPLACE(category_ids, '2,', '') as category_id FROM (
    SELECT entity_id, category_ids, (LENGTH(category_ids) - LENGTH(REPLACE(category_ids, ',', '')) + 1) as category_cnt FROM (
        SELECT entity_id, GROUP_CONCAT(category_id) as category_ids FROM (
            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)
        ) sub_query
        GROUP BY entity_id
    ) middle_query
    HAVING category_cnt = 2
) main
``
@tegansnyder

This comment has been minimized.

Copy link
Owner Author

tegansnyder commented Dec 6, 2013

products in multiple categories minus the default category

SELECT entity_id, REPLACE(category_ids, '2,', '') as category_id FROM (
    SELECT entity_id, category_ids, (LENGTH(category_ids) - LENGTH(REPLACE(category_ids, ',', '')) + 1) as category_cnt FROM (
        SELECT entity_id, GROUP_CONCAT(category_id) as category_ids FROM (
            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)
        ) sub_query
        GROUP BY entity_id
    ) middle_query
    HAVING category_cnt > 2
) main
@joelnss

This comment has been minimized.

Copy link

joelnss commented Mar 9, 2019

@tegansnyder

These are awesome. Is it possible to pull in the actual category name from the catalog_category_entity_varchar table?

@FabRisky

This comment has been minimized.

Copy link

FabRisky commented Jun 28, 2019

@tegansnyder i have the same question, how i can put category names in this sql?
SELECT entity_id, category_ids, (LENGTH(category_ids) - LENGTH(REPLACE(category_ids, ',', '')) + 1) as category_cnt FROM ( SELECT entity_id, GROUP_CONCAT(category_id) as category_ids FROM ( SELECT e.entity_id, at_category_id.category_id FROMcatalog_product_entityASe LEFT JOINcatalog_category_productASat_category_id ON (at_category_id.product_id=e.entity_id) ) sub_query GROUP BY entity_id ) final_query HAVING category_cnt > 2

@sanu76543

This comment has been minimized.

Copy link

sanu76543 commented Nov 9, 2019

Looking at FrontEnd Data you notice 2005 market size for Computers and Peripherals is smaller than Monitors. That is wrong as Monitor is a part of Peripheral. Where is the error in project setup if we assume that all calculations are done exactly as they are set by rules in the available tables?

@sanu76543

This comment has been minimized.

Copy link

sanu76543 commented Nov 9, 2019

Please solve this question.

@jaydalisay

This comment has been minimized.

Copy link

jaydalisay commented Nov 13, 2019

Possible to query all in stock products by category id?

@Jeeva-Rathinam

This comment has been minimized.

Copy link

Jeeva-Rathinam commented Dec 5, 2019

How to get best selling products list based on category-wise?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.