I have an EAV (Entity Attribute Value) based table for smartphones like the following:
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
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
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?