Magento – Collection of All Configurable Products with Children

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.

Okay, so I'm actually getting somewhere by building a completely different collection...

$category_id = 3;
$collection = Mage::getModel('catalog/category')->load($category_id)
    ->getProductCollection()
    ->addAttributeToSelect('*') // add all attributes - optional
    ->addAttributeToFilter('type_id', array('eq' => 'configurable'))
    ->addAttributeToFilter('visibility', array('in' => array(Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH, Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_SEARCH, Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_CATALOG)))
    ->addAttributeToFilter('status', array('eq' => Mage_Catalog_Model_Product_Status::STATUS_ENABLED))
    ->addStoreFilter($storeId);

And then fetch the category ID's from the loaded products:-

$cats = $product->getCategoryIds();

And loop through (as @paj suggested in the comments) to get the category data for each:-

foreach ($cats as $category_id) {
        $category = Mage::getModel('catalog/category')->setStoreId(Mage::app()->getStore()->getId())->load($category_id);             
    }

I'm then able to get what I want for each product like:-

$category->getId()
$category->getName()

Meaning my required mapping works well:-

if ($category->getId()==10) {
    $cmap = 'Clothing > Trousers';
}