0

I have an EAV (Entity Attribute Value) based table for smartphones like the following:

Image of table data

What if the user wants to see all phones with - red color - steel material

The query is like:

WHERE (attribute_id = color AND value = red)
OR
(attribute_id = material AND value = steel)

In this case the search result would also select product 2 because it has red color but I don't want this, because its material is plastic instead of steel.

You can say group results by product id column having count more than 2 rows per product.

But because it's EAV flexibility, what if the material is a new option for a product, and added to a product after a 1 million values/rows later than its early added values for this product?

I mean not all specs are one after another right? Isn't that going to slow down the search?

So I need to get products only but only with red and steel (only one option like 2. Product in the table above cant be accepted) and it doesn't matter if those options have millions rows between them it still should find all values for given values of a product.

Is there a solution?

Improve this question
3

1 Answer 1

Reset to default
1

To select more than one attribute:

FROM attribute AS color
JOIN attribute AS material
  ON color.content_id = material.content_id
   AND color.attribute_id = 'color' AND color.value = 'red'
   AND material.attribute_id = 'material' AND material.value = 'steel'

So much flexibility you can strangle yourself on the long sql queries it generates :-)

For other solutions see eav

Improve this answer
2
  • Thanks its exactly what i am looking for, is this good approach for ecommerce site filtering? i am doing tests with your given code for each request to show 20 results it takes 0.1 second, should i spend more cpu when scaling the application?
    – user208176
    Commented May 4, 2020 at 7:00
  • 1
    @user208176, the problem with EAV is that the complexity grows very fast when adding more attributes to the equation. N attributes will require N-1 joins and the table itself usually grows fast since everything is lumped into one table. If you want control over resource usage like CPU, disk etc, you should probably consider limiting the number of attributes that can be used by a query.
    – Lennart - Slava Ukraini
    Commented May 4, 2020 at 8:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.