[solved] ActiveRecord: find a model that "satisfies multiple MANY_MANY conditions"

Yii 1.1 General Discussion
1

This should have been discussed before, but I couldn’t find it in forums.

In this post it is discussed how to find a model that has specific attributes in a related table, but I need to get a model that has multiple related attributes.

I am not even sure I am explaining well, so it’s better to go by example…

I have a product model (Product)




Product

   - id

   - caption

also a product attributes model (ProductAttribute)




ProductAttribute

   - id

   - caption

also a product attribute options model (ProductAttributeOption)




ProductAttributeOption

   - id

   - product_attribute_id

   - code

   - caption

“code” is a field which defines the option uniquely in ProductAttribute context, actually the primary key in the table could be (product_attribute_id, code) since it’s unique but I added “id” because I needed to uniquely identify options in other tables and it simplified my schema

As expected, a product can be related to multiple attributes with specific values, so there is a table connecting them for which there is

also a product MANY_MANY product_attribute options model (ProductAttributesProducts)




ProductAttributesProducts

   - product_id

   - product_attribute_id

   - product_attribute_option_id

What I want to do is get a Product that’s related to two specific attribute options, ie color = blue, size = small, as in find a Product that has

(product_attribute.id,product_attribute_option.id)=(x,y) AND (product_attribute.id,product_attribute_option.id)=(j,k)

[ say I have already got the product_attribute_option.id and not (product_attribute.id, product_attribute_option.code) pair ]

Is there a way to do that using AR?

2

Any help would be really helpful… if you could point to a direction I could work it out

3

only for the record… solved without AR, using something like




SELECT product_id, count(*) AS cnt

FROM shop_product_attributes_products

WHERE

   (product_attribute_id = :size AND  product_attribute_option_id = :my_size)

   OR

   (product_attribute_id = :color AND product_attribute_option_id = :my_color)

GROUP BY product_id

HAVING cnt=2