How to make nested Where clause in Magento collection using addAttributeToFilter or similar functions

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 100开发者_如何转开发0 products matching these initial criteria?

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.