Magento – Get all Magento Categories using SQL query

category-attributedatabasemagento-1.8sql

I have this SQL to get all Magento Categories.

SELECT DISTINCT
    cc.entity_id AS id,
    cc.`value` AS path,
    cc1.`value` AS `NAME`,
    cce.`level`,
    cce.parent_id
FROM
    catalog_category_entity_varchar cc
JOIN catalog_category_entity_varchar cc1 ON cc.entity_id = cc1.entity_id
JOIN eav_entity_type ee ON cc.entity_type_id = ee.entity_type_id
JOIN catalog_category_entity cce ON cc.entity_id = cce.entity_id
WHERE
    cc.attribute_id = '57'
AND cc1.attribute_id = '41'
AND ee.entity_model = 'catalog/category'

This returns all categories, but I created a new category from Magento backend but that is not showing.

That category is published and has no products in it.
Following image is from catalog_category_entity_varchar table.

enter image description here

entity_id = 449 is showing when I run that query, because it has attribute_id = 57 and 41

But I am talking about entity_id = 452 that is not showing, because it does not have attribute_id = 57.

I want to ask Magento experts, what does attribute_id = 57 belong to? and how can I fix this query to fetch all categories?

Best Answer

The problem is that you use an implicit INNER JOIN and define the join conditions for attribute tables in WHERE clauses.

That means, if there is no value saved for one of the attributes with ID 41 and 57, you get no results.

Improved version with LEFT JOIN on the attribute tables and the entity table as main table:

SELECT DISTINCT
    main_table.entity_id AS id,
    at_path.`value` AS path,
    at_name.`value` AS `NAME`,
    main_table.`level`,
    main_table.parent_id
FROM
    catalog_category_entity main_table

JOIN
    eav_entity_type ee
ON
    main_table.entity_type_id = ee.entity_type_id AND ee.entity_model = 'catalog/category'

LEFT JOIN
    catalog_category_entity_varchar at_path
ON
    at_path.entity_id=main_table.entity_id AND at_path.attribute_id = 57

LEFT JOIN
    catalog_category_entity_varchar at_name
ON
    at_name.entity_id=main_table.entity_id AND at_name.attribute_id = 41

Now the column for attribute 57 (which is url_path) will show up as NULL in the result if there is no value.

Related Topic