Magento Filter By Relative Value

Question

I would like to filter a collection by relative value per that row. For example,

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 of that sort. I can only give it a value to compare against, not a column name. I also looked at the Zend_Db_Select method at the where clause but didn't find anything that would help. Do I actually have to go all the way down to a direct SQL query (something which is, of course, avoided at all costs)? (I'm running Magento 1.3.2.4)

Thank you.

Please be more specific. I'd say it heavily depends on about what specific kind of collection you are talking about (e.g. 'catalog/product', 'sales/order', self-made). Collections can be of very different types (usage of flat and/or EAV, using joins or not, etc.). What collection is causing the problem at hand? Are the columns you try to compare default Mage fields or custom added? Or a custom table? — Jürgen Thelen, Oct 04 '11 at 09:59

score 3 · Answer 1 · answered Oct 04 '11 at 21:42

3

Try something like

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

answered Oct 04 '11 at 21:42

Dmytro Zavalkin

5,265
1
30
34

score 0 · Answer 2 · answered Oct 03 '11 at 08:59

OK, this is probably not the ideal solution, but it's one way of getting what you need.

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

I first made a new empty data collection. Then defined the collection I was going to loop through filtering what I could first. After that I looped through that collection and any products that matched my requirements got added to the 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);

Thank you but I was looking for a way to do the `if($product->getFinalPrice() > $product->getPrice()` in the query not in PHP for performance reasons. — Max, Oct 03 '11 at 12:17

Magento Filter By Relative Value

2 Answers2

Linked