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

score 7 · Accepted Answer · answered Feb 23 '12 at 01:18

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

score 3 · Answer 2 · edited May 23 '17 at 10:29

3

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.

edited May 23 '17 at 10:29

Community

1
1

answered Feb 24 '12 at 07:56

ngocanhdoan

54
2

1

So you're saying it does work, I'm just printing the query before it's executed, and thus get misleading results? – Vitamin Feb 24 '12 at 08:05
Yes, you won't see the query string with interpolated binding which of course may irritate. For double checking, reduce your collection query by a sku or product ID with binding param. – ngocanhdoan Feb 24 '12 at 19:51

Magento collection - filter by several fields

2 Answers2