Activerecord and EAV with search

Hello. Would somebody like to answer me on question or give some advice?

In my situation I have to use EAV-model (MySQL database) to deal with dynamic attributes (for products: color, weight, height and etc.)
Model: Product, Option, Value

So, in this case I have relation: product has many options through values.
It’s okay, when I modify and print it.

But what is the best solution for search on them?
Because I have to have many inner joins with the same table - values
And each join should have own conditions, for example:

option_alias1.name = ‘color’ AND values_alias1.value = ‘100’
AND option_alias2.name = ‘weight’ AND values_alias2.value < ‘200’

So, I can’t do it by join existing relation many times, because I haven’t got access to aliases. Should I write raw sql queries or there is more convenient way?

Thank you.

Hello. Would somebody like to answer me on question or give some advice?

In my situation I have to use EAV-model (MySQL database) to deal with dynamic attributes (for products: color, weight, height and etc.)
Model: Product, Option, Value

So, in this case I have relation: product has many options through values.
It’s okay, when I modify and print it.

But what is the best solution for search on them?

Do you have to use Mysql for the search side too? Something like elasticsearch is good at searching over that sort of dataset.

Fred

Unfortunatelly, yes, I am.