Magento - get product count by attribute

Magento - get product count by attribute

October 7, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento - get product count by attribute

Title: Magento - Counting Products by Valid Attribute Values

Introduction

In Magento, managing product attributes is crucial for filtering and displaying products to your customers effectively, especially when dealing with layered navigation. However, getting the count of products that have valid attribute values can be challenging, as it often involves complex queries and filters. In this post, I’ll guide you through solving this issue step-by-step.

The Problem: Invalid Attribute Values

Your primary challenge lies in handling attributes sourced from a third party that might contain blank or “N/A” values. These invalid entries need to be excluded when counting products for specific attributes in your layered navigation.

Current Attempts and the Error

Let’s analyze the code you provided:

$productAttrs = Mage::getResourceModel('catalog/product_attribute_collection');

$x = 0;
foreach ($productAttrs as $productAttr) {
    $collection_size = Mage::getModel('catalog/product')
        ->getCollection()
        ->addAttributeToSelect($productAttr->getAttributeCode())
        ->addAttributeToFilter(
            array(
                array(
                    'attribute' => $productAttr->getAttributeCode(),
                    'notnull' => true
                ),
                array(
                    'attribute' => $productAttr->getAttributeCode(),
                    'nin' => array('N/A', '', ' ')
                )
            )
        );
    echo count($collection_size->getData());
    echo "<br>";
    $x++;
    if($x>50) {
        break;
    }
}

The error you encountered is a PDOException due to an unknown column in your SQL query. This often happens when you try to add a condition for an attribute that doesn’t exist or isn’t applicable to the current product collection.

Solving the Issue: Efficient Attribute Filtering

To resolve this, we need to approach the problem more strategically:

  1. Exclude specific attributes: Start by excluding attributes that don’t contain relevant data, such as category_id, which caused your initial error.

  2. Use a combination of filters: To exclude both null and “N/A” values, use a combination of notnull and nin (not in) filters.

  3. Avoid unnecessary attribute selection: Instead of adding all attributes to the select collection, you can specify only the required ones using addAttributeToSelect().

Here’s an improved version of your code:

$productAttrs = Mage::getResourceModel('catalog/product_attribute_collection')
    ->addFieldToFilter('attribute_code', array('neq' => 'category_ids'));

$x = 0;
foreach ($productAttrs as $productAttr) {
    if (in_array($productAttr->getAttributeCode(), array('category_id'))) continue;

    $collection_size = Mage::getModel('catalog/product')
        ->getCollection()
        ->addAttributeToSelect(array($productAttr->getAttributeCode()))
        ->addAttributeToFilter(
            array(
                'attribute' => $productAttr->getAttributeCode(),
                'notnull' => true
            )
         )
         ->addAttributeToFilter(
             $productAttr->getAttributeCode(),
             array('nin' => array('N/A', '', ' ')
         );

    echo count($collection_size->getData());
    echo "<br>";
    $x++;
    if($x>50) {
        break;
    }
}

Conclusion

In this post, we explored the challenges of counting products by valid attribute values in Magento and provided a solution to efficiently filter out invalid entries. By excluding specific attributes, using proper filters, and avoiding unnecessary attribute selection, you can achieve accurate product counts for your layered navigation.

Remember, it’s essential to test your code thoroughly on different scenarios to ensure its reliability. Additionally, consider optimizing your layered navigation by setting attribute options manually or using third-party extensions like Amasty’s Layered Navigation extension if the built-in functionality doesn’t meet your needs.

Don’t forget to check out our other Magento tutorials and guides at Laravel Company for more insights into Magento development.

Catalog Customers Errors

Our network