I am getting all product sku's that does not have a attribute value


I have written this code in magento :

$products = Mage::getModel('catalog/product')
->addAttributeToFilter('image_link', array('null' => true));  

When i Echo the sql Query I get the below query

echo $products->getSelect();

SELECT `e`.*, `at_image_link`.`value` AS `image_link` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_product_entity_text` AS `at_image_link` ON (`at_image_link`.`entity_id` = `e`.`entity_id`) AND (`at_image_link`.`attribute_id` = '143') AND (`at_image_link`.`store_id` = 0) WHERE (at_image_link.value IS NULL)

This returns me proper result .

I want to create a report based on this . Using filters . So i need to change this to a proper query in magento model something like below :

protected function _initSelect()
    $catalogProductEntitytable = Mage::getSingleton('core/resource')->getTableName('catalog_product_entity');
                            ->joinLeft( array( 'catalog_product_entity' => $catalogProductEntitytable ),
                                    'catalog_product_entity.entity_id = registry_products.product_id',
                                    array('sku' ) )
                            ->where('registry_products.product_status = ?', 'active')
                            ->where('catalog_product_entity.sku is not null')
    return $this;

This is another collection model .I tried a few code changes but nothing seems working . Can anyone guide me .Thanks.

  • Try echo $this->getSelect()->__toString() to see if your request is good or not.
    – Kozame
    Commented Sep 14, 2018 at 7:31
  • the function i have given above is just a sample from another mode . I need o create something like it from the sql query i have .
    – Melvin
    Commented Sep 14, 2018 at 7:35

1 Answer 1


Try something like this :

$attributeId = 143;
$storeId = Mage_Core_Model_App::ADMIN_STORE_ID; // default store = 0

$select = $this->getSelect()
    ->from(['e' => 'catalog_product_entity'])
    ->joinInner(['at_image_link' => 'catalog_product_entity_text'], 'at_image_link.entity_id = e.entity_id and at_image_link.attribute_id = .' $attributeId '. and at_image_link.store_id = ' . $storeId)
    ->where("at_image_link.value is null");

echo $select->__toString();
  • that gave me SELECT e.* FROM catalog_product_entity AS e WHERE (e.image_link is null) not the sql query i got from this echo $products->getSelect();.this has inner join with catalog_product_entity_text table . I am confused here
    – Melvin
    Commented Sep 14, 2018 at 7:59
  • OK, sorry, I didn't view the inner join. I updated my answer but I can't test it. Please say me if that's work for you.
    – Kozame
    Commented Sep 14, 2018 at 8:10
  • SELECT e.*, at_image_link.* FROM catalog_product_entity AS e INNER JOIN catalog_product_entity_text AS at_image_link ON at_image_link.entity_id = e.entity_id and at_image_link.attribute_id = 143 and at_image_link.store_id = 0 WHERE (e.image_link is null); i got this and when i tried in sql directly it returned ERROR 1054 (42S22): Unknown column 'e.image_link' in 'where clause'
    – Melvin
    Commented Sep 14, 2018 at 9:15
  • 1
    I modified where clause. And now ?
    – Kozame
    Commented Sep 14, 2018 at 9:21
  • perfect .That worked . great . I added the query and worked . I am able to download reports but date filters are not working . throwing me some error . Thanks
    – Melvin
    Commented Sep 14, 2018 at 9:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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