2

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?

2

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.

1

I think, there is an attribute with ID = 57. You can check it in database table eav_attribute.

In admin Category section, For entity_id = 449 (categoryId 449), there might some value set for this attribute. And for entity_id = 452 (categoryId 452), this value will be empty (I mean not set). You can add some value in this attribute for this entity, then do SAVE (and re-index if needed)

Now run query, you can see entity_id = 452.

UPDATE: Here attribute_id = 57 is category attribute : url_path. Please run Catalog URL Rewrites index. This will set the value of this attribute for All categories.

Make sure, you set Index Mode to Update on Save

  • 57 3 url_path varchar text 0 0 0 this is the ROW with ID = 57 in that table ... – Umair Jul 25 '16 at 9:01
  • its the URL PATH ... but how can I add URL path to a category in backend? is that the URL Key option in category backend? I have already set that to some value and that is not empty – Umair Jul 25 '16 at 9:02
  • I think, you need to run re-index. Check updated answer – Anil Suthar Jul 25 '16 at 9:13
  • Does magento work that way? I mean I have added URL KEY from backend and it still didnt INSERTED that row into DB? still need to re-index – Umair Jul 25 '16 at 9:16
  • Catalog URL Rewrites is set to Update on Save OR Manual Update ? If its set to Manual Update then you have to re-index manually Otherwise it will run auto on Updating/Saving category – Anil Suthar Jul 25 '16 at 9:23

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.