0

I want to get product collection which has notify_stock_qty greater than total_available_qty (custom product attribute). So I used below code-

$collection = Mage::getModel('catalog/product')->getCollection()
                                                       ->addFieldToFilter('status',1)
                                                       ->addAttributeToSelect('*')
                                                       ->joinField('notify_stock_qty',
                                                         'cataloginventory/stock_item',
                                                         'notify_stock_qty',
                                                         'product_id=entity_id',
                                                         '{{table}}.stock_id=1',
                                                         'left')
                                                        ->addAttributeToFilter('notify_stock_qty', array ('gt' => new Zend_Db_Expr('total_available_qty') ) );

This showing below error-

a:5:{i:0;s:870:"SELECT `e`.*, IF(at_status.value_id > 0, at_status.value, at_status_default.value) AS `status`, `at_notify_stock_qty`.`notify_stock_qty` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`entity_id` = `e`.`entity_id`) AND (`at_status_default`.`attribute_id` = '273') AND `at_status_default`.`store_id` = 0
 LEFT JOIN `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '273') AND (`at_status`.`store_id` = 1)
 LEFT JOIN `cataloginventory_stock_item` AS `at_notify_stock_qty` ON (at_notify_stock_qty.`product_id`=e.entity_id) AND (at_notify_stock_qty.stock_id=1) WHERE (IF(at_status.value_id > 0, at_status.value, at_status_default.value) = '1') AND (at_notify_stock_qty.notify_stock_qty > total_available_qty)

I tried below links but did not worked-

https://stackoverflow.com/questions/12148180/how-do-i-compare-2-different-fields-in-magento-in-a-collection

Filter Product Collection By Another Attribute In Product Collection eg 'attribX', array('gt' => 'attrib-Y')

I am not sure what is wrong there ?

Solution--- This worked for me---

$collection = Mage::getModel('catalog/product')->getCollection()
                                                       ->addFieldToFilter('status',1)
                                                        ->addAttributeToSelect('total_available_qty')
                                                       ->addFieldToFilter('total_available_qty',array('gteq'=>0))
                                                       ->addAttributeToSelect('*')
                                                       ->joinField('notify_stock_qty',
                                                         'cataloginventory/stock_item',
                                                         'notify_stock_qty',
                                                         'product_id=entity_id',
                                                         '{{table}}.stock_id=1',
                                                         'left');
        $collection ->getSelect()->where('`at_notify_stock_qty`.`notify_stock_qty` > `at_total_available_qty`.`value`');
0

Since you are trying to filter by the total_available_qty attribute you should use:

->addAttributeToFilter('total_available_qty', array('lt' => new Zend_Db_Expr('notify_stock_qty')))

In MySQL it is not allowable to refer to a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed.

When you were calling:

->addAttributeToFilter('notify_stock_qty', array('gt' => new Zend_Db_Expr('total_available_qty')));

total_available_qty is a column alias for at_total_available_qty.value.

So if you plan on using attributes in the WHERE condition you should use them with the full table reference.

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.