0

guys ! How can i list all categories with related products in them (i need the SKUs). Where is the relation table for catalog_category_entity(or varchar) and catalog_product_entity(or varchar). ?

I can use only SQL.. because i don't know php. Thx !

2

The category product relation table is catalog_category_product. It contains the category id and the product id its related to. Also there is the position column position which determines the order in which the products are fetched.

So to get the product SKU you need do a join with the catalog_product_entity which has the SKU's for the products.

So something like

SELECT
    * 
FROM
    `catalog_category_product` `ccp` 
JOIN
    `catalog_product_entity` as `cpe` 
        ON `cpe`.entity_id = `ccp`.`product_id`

should help you get the SKU's against the product ids. And of course you can replace the SELECT * with only the fields you like to have.

Hope this helps !!

| improve this answer | |
1

Here is mysql Query to Fetch All products with their categories from magento 1.9

SELECT product_id,product_name,product_path, GROUP_CONCAT(DISTINCT(category_id)) AS category_ids,GROUP_CONCAT(DISTINCT(category_name)) AS category_name,GROUP_CONCAT(DISTINCT(path)) AS category_path FROM (
SELECT DISTINCT B.*,cpev.`value` AS product_path FROM   (SELECT A.*,cv.value AS `category_name`,cc1.value AS path  FROM (
        SELECT
            catalog_product_entity_varchar.entity_id AS product_id,
            catalog_product_entity_varchar.`value` AS product_name,
            catalog_product_entity.sku,
            `at_category_id`.`category_id`
        FROM catalog_product_entity_varchar
        INNER JOIN catalog_product_entity ON catalog_product_entity_varchar.entity_id = catalog_product_entity.entity_id
        LEFT JOIN `catalog_category_product` AS `at_category_id` ON (at_category_id.`product_id`=catalog_product_entity.entity_id)
        WHERE catalog_product_entity_varchar.entity_type_id = (
            SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product' )
        AND catalog_product_entity_varchar.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_product' ))
    ) AS A
    INNER JOIN `catalog_category_entity` AS cat ON cat.`entity_id` = A.category_id
    JOIN `catalog_category_entity_varchar` AS cv ON cat.entity_id = cv.`entity_id`
    JOIN catalog_category_entity_varchar cc1 ON cv.entity_id=cc1.entity_id 
    JOIN `eav_attribute` AS att ON att.`attribute_id` = cv.`attribute_id`
    JOIN `eav_entity_type` AS aty ON att.`entity_type_id` = aty.`entity_type_id`
    WHERE aty.`entity_model` = 'catalog/category' AND att.`attribute_code` = 'name' AND cc1.attribute_id IN (SELECT attribute_id AS attr FROM eav_attribute WHERE attribute_code='url_path')
) AS B
JOIN catalog_product_entity_varchar AS cpev ON cpev.`entity_id` = B.product_id
WHERE cpev.`attribute_id` IN (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'url_key')) sub_query GROUP BY product_id

Hope this will help you to fetch all products with their categories using mysql query.

| improve this answer | |
0

Here is mysql Query to Fetch All products with their categories by sub-query

SELECT e.entity_id As product_id,e.type_id As product_type, e.sku,(Select GROUP_CONCAT(DISTINCT(cv.value)) FROM catalog_category_entity_varchar AS cv ,catalog_category_product AS at_category_id where at_category_id.category_id = cv.entity_id AND (at_category_id.product_id=e.entity_id) and cv.attribute_id = 41 and cv.store_id = 0 ) AS category_name FROM catalog_product_entity AS e;

This will help you to fetch all products with their categories.

| improve this answer | |
0

To help anyone looking for an answer that works with Magento 2.1.6 (and probably other 2.x versions, but untested), Vikas answer above was great, and all i had to do was change the cv.attribute_id from 41 to 33. This gave me the actual name of a category that I wanted to fetch against the SKU. You can check your database table (catalog_category_entity_varchar) for this info in the attribute_id column.

Final code I used successfully is as follows:

SELECT e.entity_id As product_id, e.sku, (Select GROUP_CONCAT(DISTINCT(cv.value)) FROM catalog_category_entity_varchar AS cv , catalog_category_product AS at_category_id WHERE at_category_id.category_id = cv.entity_id AND (at_category_id.product_id=e.entity_id) AND cv.attribute_id = 33 and cv.store_id = 0 ) AS category_name FROM catalog_product_entity AS e;

This results in a single row for a Product ID, SKU and a single column of comma separated values of all Categories that SKU is linked to.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.