如何使用 addAttributeToFilter 或类似函数在 Magento 集合中创建嵌套Where子句
我必须使用多个嵌套的 OR 和 AND 条件来过滤产品集合。我尝试使用纯 SQL 语句来做到这一点，我想我可以做到，但是当我完成今天的工作时，我想也许这里有人会知道更好的解决方案，我明天醒来就会得到答案：P。
我的问题是 Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection 类提供的 addAttributeToFilter() 方法使得只能嵌套 where 子句两次 - 所以我可以有
(cond1 OR cond2 OR cond3) AND (cond4 OR cond5) AND cond6
但不能
cond1 AND (cond2 OR(cond3 AND cond4))
（或者我不知道如何做到这一点）。
正如我所说，我开始使用纯 SQL 语句来获取产品 id，因此我可以使用“in”条件添加 AttributeToFilter。但是 Magento 数据库真是一团糟！我的意思是，除非你必须通过 googol 表跟踪每个关系，否则这很好：P。
我认为 Zend 集合对象可能有一些我需要的功能，但我对 Zend 不太熟悉（我的错）。
那么，有人可以让我免于编写 300 行 SQL 查询吗？我一直想做，但我想我可以推迟一下:)。
干杯， 马科
编辑： 我必须获取有关产品的信息，在这种组合条件下，第三方模块将会有一些自定义选项，例如：目标年龄等。我想我必须在 PHP 方面执行此操作，但我不太喜欢它 -目前，最初可以过滤 37 种产品，但如果有 1000 种产品符合这些初始条件怎么办？
I have to filter the product collection with multiple nested OR's and AND's criteria. I tried to do this using pure SQL statement and I guess I can do it, but as I am finishing work for today I thought that maybe someone here would know a better solution and I will wake up tomorrow and have the answer :P.
My problem is that addAttributeToFilter() method provided by Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection class makes it possible to only nest the where clause two times - so I can have
(cond1 OR cond2 OR cond3) AND (cond4 OR cond5) AND cond6
but not
cond1 AND (cond2 OR(cond3 AND cond4))
(or I don't know a way to do it).
As I said I started playing with pure SQL statement to get products ids, so then I can just addAttributeToFilter with 'in' condition. But Magento db is such a mess! I mean it's fine until you have to trace every relation through googol tables :P.
I thought that Zend collection objects may have some functions that I need but I am not too familiar with Zend (my bad).
So, will anyone save me from writing 300-lines SQL query? I always wanted to do it, but I think I can postpone it a little :).
Cheers,
Maćko
EDIT:
I have to get information about products and in this combined condition there will be some custom options from third party module like: targeted age etc. I think I will have to do it on PHP side, but I don't like it too much - for now there are 37 products that can be filtered initially, but what if there would be like 1000 products matching these initial criteria?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
有关您尝试检索的信息的更多详细信息将会有所帮助。 Magento 可能会为您想要的特定数据生成一些表，我们可以指向这些数据，但这在很大程度上取决于您的具体情况。
否则，您可以使用较低级别的 Zend DB 类构建条件，但这无论如何都会破坏数据库的抽象，因此可能没有太大帮助。查看平面目录和其他索引以查看这些示例。
希望有帮助！
谢谢，
乔
听起来您关心的是运行查询的效率。如果是这种情况，请确保明智地对表建立索引（使用索引和内部查询来选择尽可能少的记录）。
听起来自定义 SQL 查询（或者甚至类似于 Magento 的重新生成的索引）可能是这里的方法。
A little more detail on the information you're trying to retrieve would be helpful. It's possible that Magento has some generated table for the specific data you want that we could point to, but that's heavily dependent on your individual case.
Otherwise, you could build your conditions using the lower-level Zend DB classes, but that breaks the abstraction to the database anyway so it's probably not much help. Take a look at flat catalog and other indexing to see examples of these.
Hope that helps!
Thanks,
Joe
Sounds like your concern is efficiency in running the query as much as anything. If that's the case, make sure to index the tables wisely (use an index and an inner query that selects down to as few records as possible).
It sounds like a custom SQL query (or even a regenerated index similar to Magento's) might be the way to go here.