Magento filter by relative cost

Question

I would like to filter the collection by relative value in this row. For instance,

SELECT * FROM table WHERE column_1 > column_2 

The only thing I know how to do in Magento would be

 $q = Mage::getModel('table')->getCollection() ->addAttributeToFilter('column_1', array('gt' => $some_number)); 

or something like that. I can only give it a value for comparison, not a column name. I also looked at the Zend_Db_Select method in the where clause, but did not find anything that could help. Should I actually go to a direct SQL query (which, of course, can be avoided at all costs)? (I am running Magento 1.3.2.4)

Thanks.

Answer 1

Try something like

 $q = Mage::getModel('table')->getCollection() ->addAttributeToSelect('column_1') ->addAttributeToSelect('column_2') ->addAttributeToFilter('column_1', array('gt' => Zend_Db_Expr('`column_2`'))); 

Answer 2

OK, this is probably not the perfect solution, but this is one way to get what you need.

This is how I got a collection of products that were on sale, where the final price of the product is lower than its price.

First, I did a new empty data collection. Then I determined the collection in which I was going to get filtered, first of all. After that I went in cycles in this collection, and all products which corresponded to my requirements, were added to an empty collection.

  $this->_itemCollection = new Varien_Data_Collection(); $collection = Mage::getResourceModel('catalog/product_collection'); $collection->setVisibility(Mage::getSingleton('catalog/product_visibility')->getVisibleInCatalogIds()); $category = Mage::getModel('catalog/category')->load($this->getCategoryId()); $collection = $this->_addProductAttributesAndPrices($collection) ->addStoreFilter() ->addCategoryFilter($category) ->addAttributeToSort('position', 'asc'); $i=0; foreach($collection as $product){ if($product->getFinalPrice() > $product->getPrice() && !$this->_itemCollection->getItemById($product->getId())){ $this->_itemCollection->addItem($product); $i++; } if($i==$this->getProductsCount()){ break; } } $this->setProductCollection($this->_itemCollection);