Get product's category ids and names

Get product's category ids and names

July 13, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Get product's category ids and names

Getting Product Category IDs and Names Efficiently in Magento

In Magento, retrieving product category data is a common task for custom functionality. The Mage_Catalog_Model_Resource_Eav_Mysql4_Product::getCategoryIds() method provides a starting point, but what if you need both the category IDs and names? Let’s dive into an efficient way to achieve this using proper EAV joins.

Understanding the Current Approach

You’re currently selecting only category_id from the catalog_category_product table. To include the category name, you’ve added two additional joins - one for catalog_category_entity_varchar and another for eav/attribute. The WHERE clause filters by attribute code ‘name’. This works, but it has room for optimization.

Improving the Query Performance

The current query is correct, but there are a few areas we can improve:

  1. Avoid Unnecessary Joins: You’re joining with the eav/attribute table which isn’t necessary if you only need the category name attribute.

  2. Use Indexed Columns for Filters: The ‘name’ column in catalog_category_entity_varchar might not be indexed, causing full-table scans. To optimize this, ensure that the required columns are indexed.

  3. Avoid Unnecessary Column Selects: You’re selecting all columns from both joined tables (a and b), which can lead to unnecessary data being retrieved.

The Refactored Query

Let’s refactor your query with these optimizations:

$select = $this->_getReadAdapter()->select()
    ->from(array('cat_product' => $this->_productCategoryTable), 'category_id')
    ->where('catalog_category_product.product_id=?', $product->getId())
    ->join(
        array('cat_varchar' => $this->getTable('catalog_category_entity_varchar')),
        'cat_varchar.entity_id = catalog_category_product.category_id',
        array('name' => 'value')
    )
    ->where("cat_varchar.attribute_id = 62"); // Assuming 'name' attribute ID is 62

Here’s what we’ve done:

  • Removed the unnecessary join with eav/attribute.
  • Replaced the WHERE clause on attribute_code with a direct check on attribute_id. This assumes you know the ID of the ‘name’ attribute, which can be obtained through the Magento admin or via an API call. Using the ID is more efficient than using string matching.
  • Renamed table aliases to something more descriptive for clarity.

Conclusion

In conclusion, by understanding your current approach and applying some best practices around database query optimization, we’ve refactored your code to improve performance without sacrificing functionality. Always remember to test changes thoroughly, as attribute IDs can vary between Magento versions or customizations.

Check out our article on improving Magento performance for more tips and tricks!

Happy coding!

Catalog Database Performance

Our network