Magento 1.9 - SQL for pulling un-categorized products + Product name

Question

I am using a simple sql to find out what products still need to be categorized...

the SQL...

SELECT e.entity_id, e.sku FROM catalog_product_entity AS e LEFT JOIN catalog_category_product AS l ON l.product_id = e.entity_id WHERE l.category_id IS NULL

however im a little dumbfounded on how to add the actual product name... i tried...

SELECT e.entity_id, e.sku, e.name FROM catalog_product_entity AS e LEFT JOIN catalog_category_product AS l ON l.product_id = e.entity_id WHERE l.category_id IS NULL

however it failed. saying unknown column on e.name

Answer 1

Yes, it's a bit more complicated than that, because Magento uses EAV tables. In your case the name attribute is a varchar so it would be saved in the catalog_product_entity_varchar table.

The Easy Solution

If you are using Catalog Product Flat, you can just join on that table instead. It will contain the name AND the sku. It's called catalog_product_flat_STOREID, STOREID being the ID of the Store you are focusing on.

The bit less Easy Solution

A bit more time consuming, but the right way if you want real time data. Go to eav_attribute, search for name in the attribute_code column, then note the attribute_id value.

You can then join on the varchar table:

SELECT e.entity_id, e.sku, evar.value AS name
FROM catalog_product_entity AS e 
LEFT JOIN catalog_category_product AS l ON l.product_id = e.entity_id 
LEFT JOIN catalog_product_entity_varchar AS evar ON evar.entity_id = e.entity_id AND evar.attribute_id = NAME_ATTRIBUTE_ID_HERE AND evar.store_id = STOREID
WHERE l.category_id IS NULL

Answer 2

Put this script file in base directory in Magento and run it.

<?php 
require_once('app/Mage.php');
umask(0);
Mage::app();

$collection = Mage::getModel('catalog/product')->getCollection()
                ->addAttributeToFilter('category_ids','');

echo $collection->getSelect()->__toString();
echo '</br></br></br>';

foreach($collection as $pro){
   echo $pro->getName();
}