0

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.

  • Try echo $this->getSelect()->__toString() to see if your request is good or not. – Kozame Sep 14 '18 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 Sep 14 '18 at 7:35
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 Sep 14 '18 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 Sep 14 '18 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 Sep 14 '18 at 9:15
  • 1
    I modified where clause. And now ? – Kozame Sep 14 '18 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 Sep 14 '18 at 9:35

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.