Multi-table models in Ruby on Rails

Good day, dear reader. You are probably familiar with the popular web-framework Ruby on Rails. If not, then in this post you can find a lot of interesting and informative information. One of his rules is "One Model - One Table." Following it, the Cat model should take information from the cats table, unless another name is specified. And if our model consists of several, let's say six, tables? The standard joins / include methods of activerecord are no longer there to help.

Formulation of the problem

So, we will simulate a task for the sake of visualization of the presented solution. Suppose we have to have a Product model. The graphic structure of our model is shown in the figure:


The basis of the model is the items table. It has single links to the colors, types, stores, and manufactures tables. In turn, the last two are associated with addresses 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 having values ​​for column names or aliases. But this approach has a bunch of disadvantages. Firstly, there is no normal value search. Secondly, if there are a large number of records, you will have to create your own pagination. But many people love Rails for ActiveRecord, the will_paginate, kaminari, meta_search libraries. Therefore, you must associate our large query with ActiveRecord.

Recall Sql View

For this we need to get acquainted, if we are not already familiar, with representations in sql. View (VIEW) - a database object that is the result of executing a query to the database defined using the SELECT statement at the time the view is accessed. But also views can be mistaken for a virtual readonly table. In more detail, using mysql as an example, the presentation is described here . For us, it is important that activerecord counts it as a table and provides its powerful potential.

First, create a migration:

rails g migration AddProducts

We will write the following in it:

  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 the full power of activerecord, arel and other libraries to receive, 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, ruby ​​code is minimized. The only negative in this solution is the need to constantly recreate 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.