How to compare a product attributes with product Notify stock Qty in Magento Collection

Question

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`');

Answer 1

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.