0

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

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
  • evar.name from the first line? – user3760917 Sep 3 '15 at 16:45
  • My bad, I have just updated the query – Mayers Sep 3 '15 at 16:46
  • forgive my newbie status with SQL queries but on this line...evar.attribute_id = NAME_ATTRIBUTE_ID_HERE what ID would i want to put there? – user3760917 Sep 3 '15 at 16:54
  • Read the sentence JUST above the query. That's the attribute id. You can also find it by editing the attribute directly in the backend, it will be in the URL. – Mayers Sep 3 '15 at 16:56
0

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();
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.