SKU Report for null attribute values

Question

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

'image_link'.

I have written this code in magento :

$products = Mage::getModel('catalog/product')
->getCollection()
->addAttributeToSelect('*')
->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()
{
    $this->getSelect()->reset();
    $catalogProductEntitytable = Mage::getSingleton('core/resource')->getTableName('catalog_product_entity');
    $this->getSelect()->from($products->getSelect());
    $this->getSelect()->columns(array(
                                    'sku',
                                ))
                            ->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')
                            ->order('registry_products.registry_id');
    return $this;
}

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

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