2

What I m trying to do is a query with name,sku,publisher(custom attribute) for products with no category ids.

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 

That code gives me only sku. How can I join another table to get the others that I want? This is something I tried to do but doesn't work. attribute_id=71 is name-Title and attribute_id=137 is Publisher

SELECT e.entity_id, e.sku , d.value 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 
Inner JOIN catalog_product_entity_varchar AS d ON d.entity_id=e.entity_id WHERE d.attribute_id=71 OR d.attribute_id=137

Of course I just need the result. I don't mind if the answer is magento way. I couldn't do it either way.

0

Write php script at magento root dir.

Using magento product collection model get data quickly:

require_once 'app/Mage.php';
Mage::app("admin");
$collection=Mage::getResourceModel('catalog/product_collection')
          ->addAttributeToSelect('name')
          ->addAttributeToSelect('sku')
          ->addAttributeToSelect('custom_attribute_code');

foreach($collection as $product){
  //sku ->
  $product->getSku();
  //name ->
  $product->getName();
  // custom
  $product->getData('custom_attribute_code');
  $CategoryIds=$product->getResource()->getCategoryIds($product);
  // cateory count
  count($CategoryIds);
}
| improve this answer | |
  • _getResource() you don't need this.It is giving an error – vbak Sep 29 '15 at 18:56
  • it replace by getResource() and it's working – Amit Bera Sep 29 '15 at 19:00

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.