Magento：使用分组子句过滤集合

发布于 2024-10-20 18:23:02 字数 207 浏览 3 评论 0原文

我想用分组子句过滤集合。在 SQL 中，这看起来像：

SELECT * FROM `my_table` WHERE col1='x' AND (col2='y' OR col3='z')

如何将其“翻译”为使用 ->addFieldToFilter(...) 过滤集合？
谢谢！

原文

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!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

洛阳烟雨空心柳 2024-10-27 18:23:02

如果您的集合是 EAV 类型，那么这很有效：

$collection = Mage::getResourceModel('yourmodule/model_collection')
    ->addAttributeToFilter('col1', 'x')
    ->addAttributeToFilter(array(
        array('attribute'=>'col2', 'eq'=>'y'),
        array('attribute'=>'col3', 'eq'=>'z'),
    ));

但是，如果您坚持使用平面表，我认为 addFieldToFilter 的工作方式不完全相同。一种替代方法是直接使用选择对象。

$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('y')));

传递不带引号的值是不安全的，这里使用适配器来安全地引用它们。

最后，如果 col2 和 col3 实际上是相同的，如果您对单个列中的值进行 OR 运算，那么您可以使用以下简写：

$collection = Mage::getResourceModel('yourmodule/model_collection')
    ->addFieldToFilter('col1', 'x')
    ->addFieldToFilter('col2', 'in'=>array('y', 'z'));

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

回复收藏 0 原文

~没有更多了~