CODEWITHSUNDEEP
phpmysqljoinfilterentity-attribute-value
Juan V
Juan V

Reputation: 508

Fetching products matching multiple attribute filters (entity attribute value)

I'm trying to add the possibility to add multiple filters when fetching products from a database that has a basic entity-attribute-value model. The filters are based on attribute values like:

I have no problems with a single filter, but I don't know how to approach multiple filters at once.

EXAMPLE DATA: (reduced version)

SQL file with this example structure & data

Table products:

id name
1  jacket
2  shirt

Table product_variants:

id product_id
1  1
2  2
3  2

Table attributes:

id name
1  colors
2  sizes

Table attribute_value:

id attribute_id value
1  1            green
2  1            red
3  2            small
4  2            large

Table variant_details:

id product_variant_id attribute_value_id
1  1                  3                    (jacket - small)
2  2                  1                    (shirt - green)
3  2                  3                    (shirt - small)
4  3                  1                    (shirt - green)
5  3                  4                    (shirt - large)

For this example, the three variants would be:

THE GOAL:

I want to pass the ids for the attribute values and get the products matching ALL said attribute values, for example:

products.php?attr=1&4

Should fetch products that are green(1) AND large(4), for the example only the third variant matches the criteria, so the desired result would be:

product_id product_desc
2          shirt

WHAT I'VE TRIED:

I don't think it'll be much help but I have this for a single attribute:

products.php?attr=1

Should yield anything that has the attribute value green (1):

SELECT products.id, products.desc, attribute_value.value FROM products
LEFT JOIN product_variants ON products.id = product_variants.product_id
LEFT JOIN variant_details ON variant_details.product_variant_id = product_variants.id
LEFT JOIN attribute_value ON attribute_value.id = variant_details.attribute_value_id
WHERE attribute_value.id = 1
GROUP BY products.id

The problem with this is, before the WHERE clause, this is what it produces:

id name    value
1  jacket  small
2  shirt   green
2  shirt   small
2  shirt   green
2  shirt   large

and I don't know how to filter those rows that have the value column both green & large, for example. I'm thinking the approach I'm taking for the query is wrong.

Any help would be appreciated.

Upvotes: 2

Views: 799

Answers (1)

Juan V
Juan V

Reputation: 508

I was able to get it working in a way with the following query:

SELECT products.id, products.desc FROM products
WHERE EXISTS 
(
SELECT * FROM product_variants
LEFT JOIN variant_details ON variant_details.product_variant_id = product_variants.id
LEFT JOIN attribute_value ON attribute_value.id = variant_details.attribute_value_id
WHERE product_variants.product_id = products.id AND attribute_value.id = 1
)
AND EXISTS
(
SELECT * FROM product_variants
LEFT JOIN variant_details ON variant_details.product_variant_id = product_variants.id
LEFT JOIN attribute_value ON attribute_value.id = variant_details.attribute_value_id
WHERE product_variants.product_id = products.id AND attribute_value.id = 4
)

I say in a way because it will return all products that have every requested attribute in one or more of its variants.

Example:

Take a single product, a shirt that comes in two colors and two sizes, its variants could be:

  • shirt (green & small)
  • shirt (green & large)
  • shirt (red & small)

Notice the absence of a red & large variant. If I do the above query with red & large it would still match this product because one of its variants has 'red' and another has 'large'.

I decided to add this as an answer instead of an edit because it accomplishes the goal of matching a product with two (or more) attributes but I'd still like to see an answer where the result is restricted to match a single variant of the product, or perhaps another way to achieve this.

Upvotes: 2

Related Questions