Magento - get product count by attribute

Question

I've been searching for quite a few hours and I can't seem to come up with a good answer.

I have a bunch of different products, and a bunch of different attributes and attribute sets.

I'm looking to count the number of products that have a valid attribute value for the entire list of attributes. So, I want to loop through each attribute, then count the number of products that have that attribute, and have a good value for that attribute.

All of our attributes are sources from a third party. So, they often either leave the value blank, or they put "N/A".

The problem I am facing right now is the fact that I can't even just get the products that have that specific attribute available to them. The 'notnull' filter isn't working for me. I've tried this so many different ways. This is my current code that isn't working, but looks the most promising. I'll give you the error I am getting for this one, but if anyone has a solution I would love for you to share with me.

    $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;
        }
    }

So, for the above, I'm just showing the first 50 attributes. This specific code actually gives an error. It only gives an error after showing the count for the first attribute, so I assume this is just because the category_id attribute needs to be ignored (I'll do that now), but it doesn't look to be counting the products properly -- it seems to give a value of all products in the store, when I know not all products actually have the specific attributes in question.

Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'at_category_ids.category_ids' in 'field list'' in /var/www/html/store/lib/Zend/Db/Statement/Pdo.php:228

I'm not really sure where to go from here.

Also, the reason for me doing this..... I have layered navigation and only want to turn attributes on that are properly set in more than 200 products. I don't want all 4000 attributes to be available for layered navigation. So, if anyone has a better way of solving the layered navigation issues, I'm all ears there as well.

Thanks in advance!

Answer 1

Here's what I did. I still have to figure out what count I am going to use for the given attributes and set the attributes to be available for layered navigation, but this will go through and find out the number of products that have seemingly good values for a given attribute.

If you wanted, you could print out the number of products per attribute by including something like.....

                echo $productAttr->getAttributeCode();
                echo " : ";
                echo count($collection->getData());             
                echo "<br>";

Put this somewhere after the "collection" piece of code.

    //get list of attributes
    $productAttrs = Mage::getResourceModel('catalog/product_attribute_collection');
    $x = 0;
    $y = array();
    //go through each attribute and get the number of products that exist for that attribute
    foreach ($productAttrs as $productAttr) { 
        //exclude attributes that don't match criteria
        if($productAttr->getAttributeCode() == 'category_ids' || substr($productAttr->getAttributeCode(), 0, 2) != 'i_') {
                continue;
        }
        //get attributes that match the following
        $collection = Mage::getModel('catalog/product')->getCollection()
            ->addAttributeToSelect($productAttr->getAttributeCode())
            ->addAttributeToFilter(
                array
                (
                    array
                    (
                        'attribute' => $productAttr->getAttributeCode(), 
                        'notnull' => true
                    ),
                    array
                    (
                        'attribute' => $productAttr->getAttributeCode(),
                        'nin' => array('N/A', '', ' ')
                    )
                )
            );
        //use y array to count how many attributes have more than certain amount of products
        //this loop will give 100, 200, 300, 400, 500, 600, 700, 800, 900, and 1000
        //loops backwards and breaks if count is over the current number 
        for($z=0; $z<=10; $z++) {
            if(count($collection->getData()) > $z*100)  {
                $y[$z]++;
                // break;
            }
        }
        $x++;
    }
    //after finding out numbers, print the results
    $number_over = 0;
    foreach($y as $num) {
        $number_under = $number_over+100;
        echo "<br>Total number of attributes that are set in over " . $number_over;
        echo " products total is: " .$num;
        $number_over = $number_over + 100;
    }
}

EDIT: As I said previously, I am using icecat for product syndication. So, I only want to look at the icecat attributes, which are prefixed with "i_", without the quotes. So, I am excluding all attributes that do not have the "i_" prefix.