Jump to content

The ultimate community for Ruby on Rails developers.


Photo

ActiveRecord and EAV search

activerecord eav join

  • Please log in to reply
5 replies to this topic

#1 Metus

Metus

    Passenger

  • Members
  • 4 posts

Posted 30 October 2013 - 06:37 AM

Hello, I have a difficult question.

 

In my situtation I have to deal with EAV.

So, I we can imagine next models: Product, Option, Value

Product has many options through values.

 

And in this case how is better to search by this dynamic attributes from values?

I have to make joins to same table - values, and each join should have own conditions. So, I have to deal with aliases for this table.

But I can't access to aliases when I join tables by existing relations: .join(:values), or can I?

 

So, now I see just the one way - join without relations, but it produces doubled code.

And more - if I have a field "enabled" in options wich can be true or false, I should add this condition twice.

 

Is there more convenient way?

Thank you.



#2 james

james

    Guard

  • Moderators
  • 221 posts
  • LocationLeeds, U.K.

Posted 31 October 2013 - 02:43 PM

Hi Metus,

It is extremely difficult to answer your question as there is not enough information really. If you could answer a couple of questions and provide some actual code then someone might find it easier to help you

 

1) What is EAV?

2) Can you provide the table structures with sample data and expected results?

 

I think you are wanting to provide a has_many with conditions

From the official docs

 

class Post < ActiveRecord::Base
has_many :approved_comments, -> { where approved: true }, class_name: 'Comment'
end

Post.includes(:approved_comments)

 

This will load posts and eager load the approved_comments association, which contains only those comments that have been approved.

 

 

http://api.rubyonrai...assMethods.html

 

The above might help you get further with your requirements
 


Programming is just about problem solving!


#3 Metus

Metus

    Passenger

  • Members
  • 4 posts

Posted 01 November 2013 - 05:55 AM

EAV is this: http://en.wikipedia....ute–value_model

 

Structure:

 

products

-----------

id   |   ...

-----------

1    |  ...

2    |  ...

-----------

 

 

options

---------------

id   |  name   | enabled

---------------------------

10  |  colors  | 1

20  |  weight  | 1

 

 

values

--------------------------------------

product_id | options_id | value

--------------------------------------

1                | 10             | white

2                | 10             | black

1                | 20             | 150

 

 

Options can be enabled or not.

And now if I want find all products with white color and weight more than 50, I need 2 joins to table values but with different conditions:

INNER JOIN `values` `v1` ...

INNER JOIN `values` `v2` ...

INNER JOIN `options` `o1`

INNER JOIN `options` `o2`

WHERE `o1`.`enabled` = 1 AND

`o1`.`id` = 10 AND

`v1`.`value` = 'white' AND

`o2`.`enabled` = 1 AND

`o2`.`id` = 20 AND

`v2`.`value` > 50

 

So, I need to join one association many times.



#4 james

james

    Guard

  • Moderators
  • 221 posts
  • LocationLeeds, U.K.

Posted 01 November 2013 - 01:52 PM

That is much clearer

 

From the link I sent you in my last reply
 

 

Table Aliasing

Active Record uses table aliasing in the case that a table is referenced multiple times in a join. If a table is referenced only once, the standard table name is used. The second time, the table is aliased as #{reflection_name}_#{parent_table_name}. Indexes are appended for any more successive uses of the table name.

Post.joins(:comments)
# => SELECT ... FROM posts INNER JOIN comments ON ...

 

 

Post.joins(:special_comments) # STI
# => SELECT ... FROM posts INNER JOIN comments ON ... AND comments.type = 'SpecialComment'
Post.joins(:comments, :special_comments) # special_comments is the reflection name, posts is the parent table name
# => SELECT ... FROM posts INNER JOIN comments ON ... INNER JOIN comments special_comments_posts
Acts as tree example:
TreeMixin.joins(:children)
# => SELECT ... FROM mixins INNER JOIN mixins childrens_mixins ...
TreeMixin.joins(children: :parent)
# => SELECT ... FROM mixins INNER JOIN mixins childrens_mixins ...
INNER JOIN parents_mixins ...
TreeMixin.joins(children: {parent: :children})
# => SELECT ... FROM mixins INNER JOIN mixins childrens_mixins ...
INNER JOIN parents_mixins ...
INNER JOIN mixins childrens_mixins_2

Has and Belongs to Many join tables use the same idea, but add a _join suffix:


Post.joins(:categories)
# => SELECT ... FROM posts INNER JOIN categories_posts ... INNER JOIN categories ...
Post.joins(categories: :posts)
# => SELECT ... FROM posts INNER JOIN categories_posts ... INNER JOIN categories ...
INNER JOIN categories_posts posts_categories_join INNER JOIN posts posts_categories
Post.joins(categories: {posts: :categories})
# => SELECT ... FROM posts INNER JOIN categories_posts ... INNER JOIN categories ...
INNER JOIN categories_posts posts_categories_join INNER JOIN posts posts_categories
INNER JOIN categories_posts categories_posts_join INNER JOIN categories categories_posts_2

If you wish to specify your own custom joins using joins method, those table names will take precedence over the eager associations:

Post.joins(:comments).joins("inner join comments ...")
# => SELECT ... FROM posts INNER JOIN comments_posts ON ... INNER JOIN comments ...
Post.joins(:comments, :special_comments).joins("inner join comments ...")
# => SELECT ... FROM posts INNER JOIN comments comments_posts ON ...
INNER JOIN comments special_comments_posts ...
INNER JOIN comments ...
Table aliases are automatically truncated according to the maximum length of table identifiers according to the specific database.

 

 

Probably easier for you to read the actual explanation on the documents site http://api.rubyonrai...assMethods.html

You should be able to adapt that for your needs.

 

But please do NOT use this database design for a production website, it is massively inefficient and totally unscaleable. Fine for non web based apps but never in production website. Your site would grind to a halt very quickly


Programming is just about problem solving!


#5 Metus

Metus

    Passenger

  • Members
  • 4 posts

Posted 02 November 2013 - 11:30 AM

Thank you a lot! With this I can, probably, predict aliases for associations.

For this I'm going to create associations dynamicallly, like this:

class Entity < ActiveRecord::Base
  def self.create_values_association(association_name)
    has_many association_name, dependent: :destroy, class_name: 'Value'
  end

  create_values_association :values
  has_many :options, through: :values
end

And in client code:

Entity.create_values_association(:val1)
Entity.create_values_association(:val2)
Entity.joins [:val1, :val2]

If I improve this, I'm sure, that I can use it for flexible search.

 

Also, you said, that it's not good database design for web-sites.

But in my case I have not choise, because it's small web-site and I can use only MySQL.

Another way - create a lot of options tables for each type of entities.

But it's not the great way too, I guess.

 

Maybe, do you know the best solution in this case?



#6 james

james

    Guard

  • Moderators
  • 221 posts
  • LocationLeeds, U.K.

Posted 02 November 2013 - 06:41 PM   Best Answer

Also, you said, that it's not good database design for web-sites.

But in my case I have not choise, because it's small web-site and I can use only MySQL.

Another way - create a lot of options tables for each type of entities.

But it's not the great way too, I guess.

 

Maybe, do you know the best solution in this case?

 

 

I would de-normalise the database by getting rid of the options table and adding the fields to the products table.

 

I appreciate that the options table gives you dynamic flexibility to add new features to a product and can also provide a lookup but you can make a colour and weight lookup table so when editing/creating a new product you will still be able to select the colour and weight you will just have to add new properties and lookup tables for new options. You are sacrificing flexibility for performance and simplicity and you may well find it easier to make only certain options available on a per product basis if that is a requirement.

 

It would massively simplify your SQL and improve scale-ability. When dealing with a website scale-ability is everything regardless of the size of the website.


Programming is just about problem solving!






Also tagged with one or more of these keywords: activerecord, eav, join

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users