How to get a category listing from Magento?

How to get a category listing from Magento?

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Navigating Magento's Category and Product Hierarchy

Magento’s data model is robust, but accessing it directly from within a custom template often feels convoluted compared to more straightforward frameworks. If you are trying to render a visual tree of categories with their associated products—similar to the example where CATEGORY lists product 1 and product 2—you quickly hit the wall of Magento's layout system.

The core issue lies in how Magento handles category loading by default. The method $this->getAllCategories() is not a standard public method available on the block object within a page context in the way you might expect from a generic Object Manager call. Even if such a method existed, it would likely return a flat list of category objects without their children loaded. Magento uses a "lazy loading" strategy for categories; child categories and products are only fetched when explicitly requested via specific methods like getCategoryById or loadChildren(), and even then, the scope (store view) can complicate simple queries.

Understanding the Data Structure

The source material mentions a database structure that feels different from standard implementations, specifically tables dedicated to data types like VARCHAR or INT for performance. While modern databases like MySQL 8.0+ allow storing JSON documents directly in columns, Magento relies on normalized relational tables (eav_entity_table, catalog_category_entity, catalog_product_entity) to ensure ACID compliance and complex filtering capabilities that raw JSON cannot easily provide without significant overhead.

If you attempt to bypass the ORM and run raw SQL queries against these tables to fetch categories and products, you face two major pitfalls:

  1. EAV Complexity: Magento uses Entity Attribute Value (EAV) storage for many fields. A simple SELECT * from the entity table will not give you a clean list; you must join with attribute sets and store values, which bloats your result set.
  2. Scope Isolation: Products are tied to specific Store Views. A raw query might return products visible in the default scope that should be hidden for a specific customer group or locale if you aren't carefully handling the store_id parameter in your joins.

Implementing the Category-Product Listing

To achieve the desired output inside a showcase.phtml file, you cannot simply loop through a flat list. You must reconstruct the tree structure programmatically. The most reliable approach involves using Magento's Category resource model to load the root categories and then recursively loading their children.

Here is a pattern that works within a block context:

// Inside showcase.phtml or a block class method
$categoryResource = \Magento\Catalog\Model\ResourceModel\Category\CollectionFactory::create();
$rootCategories = $categoryResource->getRootCategoryIds()->getItems();

foreach ($rootCategories as $rootId) {
    $category = $this->getCategory($rootId); // Use getAttribute or load to ensure data
    
    // Load children recursively or via a collection
    $childrenCollection = $this->getCategoryCollection()
        ->addAttributeToSelect('*')
        ->setCategoryIdFilter([$rootId]);

    foreach ($childrenCollection as $childCategory) {
        // This is where you match products to the specific category
        $productCollection = $this->getProductCollection($childCategory);
        
        echo '<div class="category-block">';
        echo '  <h3>' . $childCategory->getName() . '</h3>';
        echo '  <ul>';
        
        foreach ($productCollection as $product) {
            echo '    <li>Product: ' . $product->getName() . '</li>';
        }
        
        echo '  </ul>';
        echo '</div>';
    }
}

Note that $this->getCategory() and $this->getProductCollection() require the block to have the appropriate Mage_Catalog_Block_Category interface or be constructed with the correct factory. If you are writing a custom page block, ensure you inject the necessary dependencies (\Magento\Catalog\Model\Category, \Magento\Catalog\Model\ResourceModel\Product) into your block class constructor.

The Limitation of Static Blocks and Templates

You mentioned hoping to use something like $this->getChildProducts(). This method exists on Category objects, but it is often inaccessible if the Category object hasn't been explicitly loaded with its children in the current scope. Magento optimizes for performance by not loading child data until needed. If your layout XML simply calls a block without initializing the category state correctly, these methods will return empty or throw exceptions.

The "different database organization" you observed regarding VARCHAR/INT tables likely refers to the separation of attribute values into their own tables (eav_attribute_value). This design prevents the need for massive SELECT statements that scan millions of rows in a single query. Instead, Magento allows efficient filtering and sorting by joining these smaller, indexed tables. While this makes raw SQL difficult, it is why using the Model layer (even with some extra steps to load children) is the only sustainable path forward.

If you need a simpler solution without writing recursive PHP logic, consider creating a custom block that extends Magento\Catalog\Block\Category and overriding its toHtml() method to render your specific visual representation. This keeps the heavy lifting within the framework's optimized data access layers rather than relying on fragile raw SQL queries that might break with future updates.

Our network