As probably you know (or maybe not) in Magento you can add your own filters to every collection, basically it looks like:
$collection = Mage::getModel('catalog/product')->getCollection();
$collection->addAttributeToFilter('myfield', array('eq' => 'test'));
So here is full list of conditions you may use:
1. “eq” – equals (x = 1): addAttributeToFilter(‘status’, array(‘eq’ => 1))
2. “neq” – not equals (x != 1): addAttributeToFilter(‘sku’, array(‘neq’ => ‘test’))
3. “like” – same as in sql, you can use % here: addAttributeToFilter(‘sku’, array(‘like’ => ‘%test%’))
4. “nlike” – not like: addAttributeToFilter(‘sku’, array(‘nlike’ => ‘%test%’))
5. “in” – same as IN() used in sql, accepts array of values: addAttributeToFilter(‘id’, array(‘in’ => array(1,2,3)))
6. “nin” – not in: addAttributeToFilter(‘id’, array(‘nin’ => array(1,2,3)))
7. “null” – field should equal to null: addAttributeToFilter(‘description’, ‘null’)
8. “notnull” – field should be different then null: addAttributeToFilter(‘description’, ‘notnull’)
9. “gt” – greater then (x > 1): addAttributeToFilter(‘id’, array(‘gt’ => 1))
10. “lt” – less then (x < 1): addAttributeToFilter(‘id’, array(‘lt’ =>1))
11. “gteq” – greater or equal (x >= 1): addAttributeToFilter(‘id’, array(‘gteq’ => 1))
12. “lteq” – less or equal (x <= 1): addAttributeToFilter(‘id’, array(‘lteq’ => 1))
If you want to see executed SQL try:
echo $collection->getSelect();