[RUBY] [Rails] Implement a counter that counts the parent category when registering a child category (ancestry, counter

When there is a multi-level category like meat> chicken> breast After registering the breast meat category in the recipe, I implemented a function to count the parent category meat> chicken as well.

I couldn't find an article that added a counter to a multi-level category using ancestry, so I investigated it myself and implemented it, so I will explain it.

If you have a similar person, please refer to it.

Introduction

environment

Ruby: 2.6.6
Rails: 6.0.2.2

Premise

We are creating a site to post recipes.
There is a recipe DB and a category DB.
Recipes come in multiple categories. (Intermediate table)
The category is a multi-level category. Example: Meat> Chicken> Breast
Implemented with ancestry (route enumeration model)
A counter is implemented to see how many recipes there are in one category.

Gem you are using

ancestry: 3.0.7
counter_culture: 2.5.1

Click here for the introduction of counter_culture. Aggregation of the number of related records (counter cache) --Qiita

Current status

Recipes Table

Table name: recipes

 id            :bigint       not null, primary key
 title         :string       not null
 description   :string       not null

Intermediate table between Recipe and Category Multiple categories are registered for one recipe.

Table name: recipe_categories

 id          :bigint           not null, primary key
 recipe_id   :bigint           not null
 category_id :bigint           not null

Categories Table

Table name: categories

 id            :bigint           not null, primary key
 name          :string           not null
 ancestry      :string
 recipes_count :integer          default(0), not null

Model

class Recipe < ApplicationRecord
  has_many :recipe_categories, dependent: :destroy
end

class Category < ApplicationRecord
  has_ancestry
  has_many :recipe_categories, dependent: :destroy
end

class RecipeCategory < ApplicationRecord
  belongs_to :recipe
  belongs_to :category
  counter_culture :category, column_name: :recipes_count
end

current problem

If it is a 1: 1 relationship, it will be counted just by putting the following in the intermediate table.

counter_culture :category, column_name: :recipes_count

This time, I would like to add a function that counts the parent category at the same time.

For example The teriyaki chicken recipe has a breast meat category.

Recipe.find(1)
=> #<Recipe
 id: 1,
 title: "Teriyaki chicken"
>

RecipeCategory.find(1)
=> #<RecipeCategory
 id: 1,
 recipe_id: 1,
 category_id: 20
>

Category.find(20)
=> #<Category
 id: 20,
 name: "Breast meat",
 ancestry: "1/10",
 recipes_count: 1
>

The breast meat category has the following relationship.

id:1 > id:10 > id:20 Meat> Chicken> Breast

The "breast meat" associated with the 1: 1 counter is counted, but not the "meat" and "chicken". I implemented it so that the parent categories "meat" and "chicken" are also counted.

How to implement counters for parent and child categories

In conclusion, I implemented it like this.

class RecipeCategory < ApplicationRecord
  belongs_to :recipe
  belongs_to :category
  counter_culture :category, column_name: :recipes_count,
    foreign_key_values: proc { |category_id| Category.find(category_id).path_ids }
end

foreign_key_values is an option to overwrite foreign keys.

The normally associated category_id: 20 becomes a foreign key, and the Category id: 20 count increases or decreases. If you pass a number in array format to foreign_key_values, the passed value will be used as a foreign key to increase or decrease the count of all targets.

Since we want to count all parent and child categories, we implement it by passing the value of [1, 10, 20] in the example.

Implementation description

I referred to the official reference and the article that was translated and explained. GitHub - magnusvk/counter_culture: Turbo-charged counter caches for your Rails app. High-performance counter cache for Rails gem'counter_culture' README (translation) ｜ TechRacho

foreign_key_values: proc { |category_id| Category.find(category_id).path_ids }

proc { |category_id| } First, pass the argument with proc, at this time the foreign key at normal time (20 in this case) is passed

Category.find(category_id) Get the target record object.

.path_ids If you do path_ids with the ancestry function, you can get the ID of the parent-child relationship of the object in a list.

Reference: [Translation] Gem Ancestry Official Document --Qiita

When you run it, you can see that => [1, 10, 20] is returned.

Category.find(20).path_ids
  Category Load (1.0ms)  SELECT "categories".* FROM "categories" WHERE "categories"."id" = $1 LIMIT $2  [["id", 20], ["LIMIT", 1]]
=> [1, 10, 20]

You can now count all parent-child categories by passing [1, 10, 20] to foreign_key_values:.

Implementation of count recalculation function

counter_culture has a function to recalculate the count, counter_culture_fix_counts. I use it to count existing data and correct count deviations, but I can't use this feature with the foreign_key_values option.

RecipeCategory.counter_culture_fix_counts
=> Fixing counter caches is not supported when using :foreign_key_values;
you may skip this relation with :skip_unsupported => true

After investigating, when using foreign_key_values, it seems that there is no choice but to implement the recalculation function by myself, so I implemented it.

From the conclusion, I implemented it like this. Since everything has been recalculated, it may take some time if there are many cases. Please let me know if there is a good implementation method.

class RecipeCategory < ApplicationRecord

  #...
  
  #Category recipes_Recalculate all counts
  def self.fix_counts
    Category.update_all(recipes_count: 0)

    target_categories = pluck(:category_id)
    #Calculate the number of categories=> { 1: 10, 10: 3, 20: 1 }
    count_categories = target_categories.group_by(&:itself).transform_values(&:size)
    count_categories.each do |category_id, count|
      count_up_categories = Category.find(category_id).path_ids
      Category.update_counters(count_up_categories, recipes_count: count)
    end
  end
end

The contents that are being executed are as follows.

Set all counters to 0
Get the category_id in the intermediate table as an array
Calculate the number => {1: 10, 10: 3, 20: 1}
Get an array of parent-child category IDs for each category
Increase the count of all parent and child categories by the number

For the method of calculating the number of 3, I referred to the following article. Count how many identical elements are in the array-patorash blog

For how to increase the count of 5 by the number, refer to the following article. [Rails + MySQL] Counter implementation [If not, I want to create a new one, if there is, I want to increment it appropriately] --Qiita ActiveRecord::CounterCache::ClassMethods

Now, when you execute RecipeCategory.fix_counts, the count will be recalculated.

Conclusion

By setting RecipeCategory as follows, the counter function of parent-child category could be implemented.

class RecipeCategory < ApplicationRecord
  belongs_to :recipe
  belongs_to :category
  counter_culture :category, column_name: :recipes_count,
                             foreign_key_values: proc { |category_id| Category.find(category_id).path_ids }

  #Category recipes_Recalculate all counts
  def self.fix_counts
    Category.update_all(recipes_count: 0)

    target_categories = pluck(:category_id)
    #Calculate the number of categories=> { 100: 3, 102: 2 }
    count_categories = target_categories.group_by(&:itself).transform_values(&:size)
    count_categories.each do |category_id, count|
      count_up_categories = Category.find(category_id).path_ids
      Category.update_counters(count_up_categories, recipes_count: count)
    end
  end
end

Please refer to it. If you have any FBs or improvements, please let us know in the comments.

[RUBY] [Rails] Implement a counter that counts the parent category when registering a child category (ancestry, counter_culture)