I want to reduce the number of unnecessary queries. From considering counter_cache to introducing counter_culture.

The problem that was happening

I am making an app that imitates Instagram with Rails. The database structure looks like this.

When I implemented such a method on the top page (post list) of this application,

  - if post.images.count >= 2
    - #processing

The query ended up like this ^^;

The count of images is spinning a lot ... ^^;

By the way, I've heard that Rails has a function called counter_cache which is useful in such cases, so I investigated it at the beginning of this time.

The execution environment is as follows.

• Rails 5.2.3
• Ruby 2.6.0

About counter_cache

First, as a means to solve the above problem

--Use counter_cache (Rails default feature) -Use counter_culture (gem) -Write Gorigori Code

I found that there are three types. (Thank you for telling me the third one!)

First, Rails' default function, counter_cache, is implemented as follows.

models/image.rb

class Image < ApplicationRecord
  belongs_to :post, counter_cache: true
end

Write counter_cache: true in the model with dependents (belongs_to).

models/post.rb

class Post < ApplicationRecord
  has_many :images
end

The model of has_many is the same, but here we will create a column called child table name_count. In this case, create a column called images_count in the posts table.

$ rails g migration AddColumnToPost images_count:integer

By doing this, the number of images that post has will be recorded in the images_count column.

In a view, writing this way should reduce the number of queries without calling the images table each time ...

- if post.images_count >= 2
  -#processing

I noticed that I wrote so far. ** What if I already have post data? .. .. **: sob:

Yes, that's exactly the weakness of counter_cache, and if you already have data in the posts table, the value of images_count will not be updated automatically.

I also found How the ancestors had a lot of trouble and fixed it, but the structure was so mysterious that I couldn't use it. .. ..

So, as I investigated further, I came across the following counter_culture.

counter_cache supplement

For counter_cache, there were various options such as changing the default column name, so please see here for details.

Rails Guide --4.1.2.3: counter_cache

About counter_culture

counter_culture is a high-performance gem that reinforces the weaknesses of counter_cache above.

▶ ︎Official document here

Introduction method

gemfile / migration

The installation method is as follows. First, put the following in Gemfile and bundle install.

Gemfile

gem 'counter_culture'

Then generate the migration with the following command.

$ rails generate counter_culture Post images_count

As a result of this command, the following migration will be created.

XXXXXXXXXXXXXXX_add_images_count_to_posts.rb

class AddImagesCountToPosts < ActiveRecord::Migration[5.2]
  def self.up
    add_column :posts, :images_count, :integer, null: false, default: 0
  end

  def self.down
    remove_column :posts, :images_count
  end
end

If it is a migration file with the same structure, it does not seem that you have to create it with a command ^ ^

rails db: migrate.

model Describe as follows in each model file.

models/image.rb

class Image < ApplicationRecord
  belongs_to :post
  counter_culture :post
end

models/post.rb

class Post < ApplicationRecord
  has_many :images
end

Other

And if you already have a lot of data in the posts table, execute the following on the console.

$ rails c
pry(main)> Image.counter_culture_fix_counts

This will update the contents of the images_count column of the posts table.

Finally, if you write like this in the view ...

- if post.images_count >= 2
  -#processing

The number of queries has decreased! !! : smiley:

Supplementary materials, reference materials, etc.

There seemed to be various options for counter_culture, so if you want to know more, please see the official document.

• counter_culture

In addition, the articles that I referred to this time are as follows.

-High-performance counter cache for Rails gem ‘counter_culture’ README (translation) -Aggregate the number of related records (counter cache)

Others, impressions, etc.

Before this implementation, there was an N + 1 problem, so I will share the solution for your reference.

-I put in a gem bullet (for those who don't notice the N + 1 problem)

I wanted to reduce the number of queries, which was one of my goals this year, but I'm glad I found a quick way to reduce it before studying SQL.

In the future, I would like to do my best to study SQL.