SQL code to get the Product details with category tree heirarchy

Question

I'm using Magento 1.9, We have a requirement to show the product details with category tree heirarchy. Have searched this in google and haven't find any answers.

I have to create a report(using a BI tool) ,So i need to find a way to do this in pure SQL.

Does anyone have any suggestions on how to achieve this?

Below is the expected sample of expected output.There are 3 levels of categories and main category. So i want the Products which is the lowest level to be under the corresponding categories in a tree structure.

Answer 1

As the nesting level of the categories is limited, an approach with joins can be taken. Otherwise, a CTE would be a better approach.

My suggestion is based on the product category association. For each category, the parent is joined and the parent of the parent. The select gets the name of the category depending on the nesting level of the association.

SELECT
    CASE
        -- association level 1 category
        WHEN parent.entity_id IS NULL THEN category.name
        -- association level 2 category
        WHEN grand_parent.entity_id IS NULL THEN parent.name
        -- association level 3 category
        ELSE grand_parent.name
    END AS level_1,
    CASE
        -- association level 1 category
        WHEN parent.entity_id IS NULL THEN NULL
        -- association level 2 category
        when grand_parent.entity_id IS NULL THEN category.name
        -- association level 3 category
        ELSE parent.name
    END AS level_2,
    CASE
        -- association level 3 category
        WHEN grand_parent.entity_id IS NOT NULL THEN category.name
        -- association level 2 or 1 category
        ELSE NULL
    END AS level_3,
    product.*
FROM catalog_category_product AS ccp
    JOIN magento_dev.catalog_product_flat_1 AS product
        ON ccp.product_id = product.entity_id
    JOIN catalog_category_flat_store_1 AS category
        ON ccp.category_id = category.entity_id
            AND category.is_active = 1
    LEFT JOIN catalog_category_flat_store_1 AS parent
        ON category.parent_id = parent.entity_id
            AND parent.level >= 2
    LEFT JOIN catalog_category_flat_store_1 AS grand_parent
        ON parent.parent_id = grand_parent.entity_id
            AND grand_parent.level >= 2

As you see, the flat tables are used (to access the category name directly and not with another join), which requires up to date indexes. During my tests, the table catalog_category_product was also updated only through reindexing.

I'm not sure if this is what you expected/requested, but I hope it gives you a hint.