Magento:使用分组的子句过滤集合 [英] Magento: Filtering a Collection with grouped Clauses
问题描述
我要过滤带有分组子句的集合。在SQL中,它看起来像:
SELECT * FROM`my_table` WHERE col1 ='x'AND(col2 ='y 'OR col3 ='z')
如何转换 code> - > addFieldToFilter(...)?
谢谢!
如果你的集合是一个EAV类型,那么效果很好:
$ collection = Mage :: getResourceModel 'yourmodule / model_collection')
- > addAttributeToFilter('col1','x')
- > addAttributeToFilter(array(
array('attribute'=>'col2' 'eq'=>'y'),
array('attribute'=>'col3','eq'=>'z'),
)
然而,如果你坚持使用一个平台,我不认为 addFieldToFilter
以相同的方式工作。一个选择是直接使用select对象。
$ collection = Mage :: getResourceModel('yourmodule / model_collection')
- > addFieldToFilter('col1','x');
$ collection-> getSelect()
- > where('col2 =?','y')
- > orWhere('col3 =?','z') ;
但是失败的是操作符的顺序。你将得到一个查询如 SELECT * FROM my_table WHERE(col1 ='x')AND(col2 ='y')OR(col3 ='z')
。 OR
在这里不优先,绕过它意味着更具体...
$ collection = Mage :: getResourceModel('yourmodule / model_collection')
- > addFieldToFilter('col1','x');
$ select = $ collection-> getSelect();
$ adapter = $ select-> getAdapter();
$ select-> where(sprintf('(col2 =%s)OR(col3 =%s)',$ adapter-> quote('x'),$ adapter-> quote ')));
传递未引用的值是不安全的,此处使用适配器安全地引用它们。 p>
最后,如果 col2
和 col3
如果你在单个列中为OR值,那么你可以使用这个缩写:
$ collection = Mage: :getResourceModel('yourmodule / model_collection')
- > addFieldToFilter('col1','x')
- > addFieldToFilter('col2','in'=& ,'z'));
I would like to filter a collection with grouped clauses. In SQL this would look something like:
SELECT * FROM `my_table` WHERE col1='x' AND (col2='y' OR col3='z')
How can I "translate" this to filtering a collection with ->addFieldToFilter(...)
?
Thanks!
If your collection is an EAV type then this works well:
$collection = Mage::getResourceModel('yourmodule/model_collection')
->addAttributeToFilter('col1', 'x')
->addAttributeToFilter(array(
array('attribute'=>'col2', 'eq'=>'y'),
array('attribute'=>'col3', 'eq'=>'z'),
));
However if you're stuck with a flat table I don't think addFieldToFilter
works in quite the same way. One alternative is to use the select object directly.
$collection = Mage::getResourceModel('yourmodule/model_collection')
->addFieldToFilter('col1', 'x');
$collection->getSelect()
->where('col2 = ?', 'y')
->orWhere('col3 = ?', 'z');
But the failing of this is the order of operators. You willl get a query like SELECT * FROM my_table WHERE (col1='x') AND (col2='y') OR (col3='z')
. The OR
doesn't take precedence here, to get around it means being more specific...
$collection = Mage::getResourceModel('yourmodule/model_collection')
->addFieldToFilter('col1', 'x');
$select = $collection->getSelect();
$adapter = $select->getAdapter();
$select->where(sprintf('(col2 = %s) OR (col3 = %s)', $adapter->quote('x'), $adapter->quote('y')));
It is unsafe to pass values unquoted, here the adapter is being used to safely quote them.
Finally, if col2
and col3
are actually the same, if you're OR-ing for values within a single column, then you can use this shorthand:
$collection = Mage::getResourceModel('yourmodule/model_collection')
->addFieldToFilter('col1', 'x')
->addFieldToFilter('col2', 'in'=>array('y', 'z'));
这篇关于Magento:使用分组的子句过滤集合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!