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

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

Well it's actually not an issue it's the way PDO/MySQL engine is working with query statement preparation and binding parameters - which are submitted separately - and query execution afterwards.

So it's not on the DB abstraction layer to generate the final query statement if you're using Bind Parameters

See this stackoverflow question and PDO manual.

Answer 2

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