Magento collection - filter by several fields

Question

Using Magentos collection models, how should I go about adding a query part/filter such as this:

WHERE (main_table.x < 1 OR (main_table.x - main_table.y) >= 5)

Update I'm now running this:

$this->getSelect()
    ->where('main_table.x < 1')
    ->orWhere('(main_table.x - main_table.y) >= :qty');
$this->addBindParam(':qty', $qty);

Result:

SELECT ... WHERE ... AND ... AND (main_table.x < 1) OR ((main_table.x - main_table.y) >= :qty) ORDER BY ...

The issue is that I can't seem to get to bind $qty to :qty

Update 2 I ended up with this, since I needed the OR within parentheses

$this->getSelect()->where('(main_table.x < 1 OR (main_table.x - main_table.y) >= ?)', $qty);

Answer 1

When you use the getSelect method you're by-passing Magento's model collection interface. Sometimes this is the only way to get the exact select query you want, but keep in mind it may not gel 100% with what the Magento model interface is doing.

When you use the bindParamater method you're using the Magento model interface. I can't speak to why it's not working, but I suspect the Zend select object and the Magento model collection objects bind their paramaters at different times, and in a different way.

To get the results you want, skip the bindParamater method and use the simpler ? parameter replacement of the orWhere method.

$this->getSelect()
    ->where('main_table.x < 1')
    ->orWhere('(main_table.x - main_table.y) >= ?',$qty);