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.

Image from Gyazo

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 ^^;

Image from Gyazo

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.

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:

Image from Gyazo

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.

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.

Recommended Posts

I want to reduce the number of unnecessary queries. From considering counter_cache to introducing counter_culture.
I want to display the number of orders for today using datetime.
I want to output the day of the week
I want to var_dump the contents of the intent
I tried to reduce the capacity of Spring Boot
I want to know the answer of the rock-paper-scissors app
I want to display the name of the poster of the comment
I want to be aware of the contents of variables!
I want to return the scroll position of UITableView!
I want to output the character number from the left where an arbitrary character string appears.
I want to expand the clickable part of the link_to method
I want to change the log output settings of UtilLoggingJdbcLogger
I want to call a method and count the number
I want to narrow down the display of docker ps
[Ruby] I want to reverse the order of the hash table
I want to temporarily disable the swipe gesture of UIPageViewController
I want to return a type different from the input element with Java8 StreamAPI reduce ()
I want to recreate the contents of assets from scratch in the environment built with capistrano
[Controller] I want to retrieve the numerical value of a specific column from the DB (my memo)
I want to understand the flow of Spring processing request parameters
The story of Collectors.groupingBy that I want to keep for posterity
I want to graph the number of photo AC downloads [Scraping implementation] ~ 10 lines per day coding ~
I want to limit the input by narrowing the range of numbers
I want to control the default error message of Spring Boot
I want to change the value of Attribute in Selenium of Ruby
[Android] I want to get the listener from the button in ListView
I want to graph the number of photo AC downloads [MySQL ring cooperation] ~ Coding 10 lines a day ~
I want to know the JSP of the open portlet when developing Liferay
[Ruby] I want to extract only the value of the hash and only the key
I want to mess with Permission of Windows directory from WSL (ubuntu)
I want to pass the argument of Annotation and the argument of the calling method to aspect
I want to get the field name of the [Java] field. (Old tale tone)
I translated the grammar of R and Java [Updated from time to time]
I want to find out what character the character string appears from the left
I want you to use Enum # name () for the Key of SharedPreference
How to determine the number of parallels
The process of introducing Vuetify to Rails
[RxSwift] I want to deepen my understanding by following the definition of Observable
I want to get the value of Cell transparently regardless of CellType (Apache POI)
I want to control the start / stop of servers and databases with Alexa
I want to get only the time from Time type data ...! [Strftime] * Additional notes
I want to separate the handling of call results according to the API caller (call trigger)
I want to see the contents of Request without saying four or five
I want to recursively get the superclass and interface of a certain class
Run R from Java I want to run rJava
[Rails] I tried to raise the Rails version from 5.0 to 5.2
I want to graduate from npm install properly [2020]
The story of introducing Ajax communication to ruby
I want to play with Firestore from Rails
I want to write quickly from java to sqlite
I want to truncate after the decimal point
I want to reduce simple mistakes. To command yourself.
I checked the number of taxis with Ruby
I want to get the value in Ruby
[Ruby] I want to make a program that displays today's day of the week!
Rails The concept of view componentization of Rails that I want to convey to those who want to quit
[Active Admin] I want to specify the scope of the collection to be displayed in select_box
[Rails] I want to display the link destination of link_to in a separate tab
# 1_JAVA I want to get the index number by specifying one character in the character string.
From the introduction of devise to the creation of the users table
I want to call a method of another class