Multi-table models in Ruby on Rails

Good day, dear reader. You're probably familiar with the popular Ruby on Rails web framework. If not, then in this post you will be able to find a lot of interesting and informative information. One of its rules is “One model - one table”. Following it, the Cat model should take information from the cats table, unless otherwise specified. And if our model consists of several, say six tables? Standard joins / include activerecord methods are no longer a help.

Formulation of the problem

So, let's model ourselves a task for clarity of the presented solution. Suppose we should have a model of Product. The graphic structure of our model is shown in the figure:





The basis of the model is the items table. It is connected by single relationships with the colors, types, stores and manufactures tables. In turn, the latter two are linked to addresses by polymorphic links.

')

One solution in this case may be to create a simple request of this nature:

ActiveRecord::Base.connection.execute('SELECT items.*, colors.name as color, types.name as type, stores.name as store_name ... FROM items LEFT JOIN colors ON items.color_id = colors.id LEFT JOIN types ON items.type_id = types.id LEFT JOIN stores ON items.store_id = stores.id ... 

In response, we get a collection of hashes, with keys that have the meaning of column names or aliases. But this approach has a lot of flaws. First, there is no normal search by value. Secondly, if there are a large number of records, you will have to create your own pagination. But many people like Rails for ActiveRecord, the libraries will_paginate, kaminari, meta_search. Therefore it is necessary to associate our large query with ActiveRecord.

Recall Sql View

For this we need to get acquainted, if not yet familiar, with the views in sql. View (VIEW) - a database object that is the result of a query to a database, defined using the SELECT statement, at the time of accessing the view. But also representations can be taken as a virtual readonly-table. In more detail, using the example of mysql, the presentation is told here . For us, it is important that activerecord considers it a table, and will provide its powerful potential.



First, let's create a migration:

 rails g migration AddProducts 

In it we will write the following:

  def up execute ' CREATE VIEW products AS SELECT i.id AS id, i.name AS name, i.weight AS weight, i.size AS size, c.name as color, t.name as type, s.name AS store_name, sa.street AS store_street, sa.city AS store_city, sa.country AS store_country, sa.phone AS store_phone, m.name AS manufacture_name, ma.street AS manufacture_street, ma.city AS manufacture_city, ma.country AS manufacture_country, ma.phone AS manufacture_phone FROM items AS i LEFT JOIN colors AS c ON i.color_id = c.id LEFT JOIN types AS t ON i.type_id = t.id LEFT JOIN stores AS s ON i.store_id = s.id LEFT JOIN addresses AS sa ON s.id = sa.addressat_id AND sa.addressat_type = "Store" LEFT JOIN manufactures AS m ON i.manufacture_id = m.id LEFT JOIN addresses AS ma ON m.id = ma.addressat_id AND ma.addressat_type = "Manufacture" ' end def down execute 'DROP VIEW products ' end 

After we create the Product model, making it readonly:

 class Products < ActiveRecord::Base # Prevent creation of new records and modification to existing records def readonly? return true end # Prevent objects from being destroyed def before_destroy raise ActiveRecord::ReadOnlyRecord end end 

Now we can use all the power of activerecord, arel and other libraries to get, filter and paginate our complex model.

 blue_guitars_in_kiev = Product.where(:color => 'Blue', :type => 'Guitar', :store_city => 'Kiev') blue_guitars_in_kiev.each do |product| puts product.store_name end 

Requests are executed quickly, in a single transaction, the code in ruby ​​is minimized. The only minus in this solution is the need to constantly re-create this virtual table when changing the name or deleting a column.



That's all. I hope my post was useful to you, all the best.