Find all Simple Products without category. Assign Cat to simple Product who is child of configurable

Question

We have many Simple Products without an assigned category. Now i need to find all Simple Products by scripts which don´t have a category.

My idea was:

Find all configurable products who have simple products. Read the categoryid of the configurable and assign it to the childs simple products.

How can I get all configurables with childs? How can I assign the categorys to simple products by script?

Andre

Answer 1

Firstly, note that it is not needed to attach, to a category, a simple product, which belongs to a parent product, if that simple product is set to be 'not visible individually'. It serve no purpose, and just clutters the category display in admin.

If I recall correctly, magento indexing will also not index that product.

To get products not assigned to any category (as asked in the subject of this question):

Magento stores product to category relations in the table catalog_category_product

What you can do is get all the product_ids from that table, and then get all products from the catalog products table, that is not in that list: thus all the products not assigned to a category.

You can do that using a collection, with a subquery.

The following is an extract from my Dynamic Category Products extension (you need this extension ;) ), which starts with a standard category product collection, $collection = $category->getProductCollection() and alters that to the required subquery. I do it this way for specific reasons in context the extension. It can be done other ways, or via vanilla sql.

Since my extension works on categories, it uses the category product collection. You should be able to use the same idea on a normal product collection.

here goes. [code extracted from dynamic category products extension]

$collection = $category->getProductCollection();
$subSelect = $collection->getSelect();
// remove any limiters
$subSelect->reset(Zend_Db_Select::LIMIT_COUNT);
$subSelect->reset(Zend_Db_Select::COLUMNS);
//add in column we want
$newColumns = array(
    '0' => array(
        'e',
        'product_id',
        null));
$subSelect->setPart(
    Zend_Db_Select::COLUMNS,
    $newColumns
);
$fromPart = $subSelect->getPart(Zend_Db_Select::FROM);
// reset any from and grouping
$subSelect->reset(Zend_Db_Select::FROM);
$subSelect->reset(Zend_Db_Select::GROUP);
// inject my from table
$fromPart['e']['tableName'] = "catalog_category_product";
$subSelect->setPart(
    Zend_Db_Select::FROM,
    $fromPart
);
// get a product collection, and add in the above as a subquery
$productCollection = mage::getModel('catalog/product')
                ->getCollection();
$productCollection->getSelect()->where(
                'e.entity_id NOT IN (?)',
                new Zend_Db_Expr($subSelect->__toString())
);

The resulting $productCollection would be all products that are not assigned to any category. You can place other filters to the $productCollection, to limit selection to product_type, or any other product related filter.

Hope this helps you onto a solution, and please ask for any clarification.