Erase N + 1 in acts_as_tree of tree structure Gem of Ruby on Rails

Operating environment at hand

Of course, it is not possible to put the code that is in service, so I write similar code in my environment and verify the operation.

Tree structure

Looking at the old code, there are many anti-patterns. This time as well, an adjacency list, which is one of the anti-patterns, was used. In addition, the N + 1 problem was neglected and it was decided to deal with it because of poor performance. This time, there were only two levels in the specifications, so there seems to be no problem with the adjacency list.

GitHub https://github.com/amerine/acts_as_tree

How to use

https://github.com/amerine/acts_as_tree#example Quote

class Category < ActiveRecord::Base
  acts_as_tree order: "name"
end

root      = Category.create("name" => "root")
child1    = root.children.create("name" => "child1")
subchild1 = child1.children.create("name" => "subchild1")

root.parent   # => nil
child1.parent # => root
root.children # => [child1]
root.children.first.children.first # => subchild1

Premise

Let parent_id (or any key) be the parent key of the tree structure.

schema example

  create_table "technologies", force: :cascade do |t|
    t.string "name"
    t.bigint "parent_id"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["parent_id"], name: "index_technologies_on_parent_id"
  end

How to erase N + 1

The children method will be created by the ʻacts_at_tree` added to the model, so include it and associate it so that it will be read in advance with the minimum number of queries.

Resolution code

# model
class Technology < ApplicationRecord
  acts_as_tree

  def dig
    if children.empty?
      self
    else
      children.map(&:dig)
    end
  end
end

I will give you includes.

# console
Technology
  .where(parent: nil)
  .includes(children: { children: [:children] }))
  .map(&:dig)

response If you extract the two-layer code and look at it, it will look like this.

{
  "id":6,
  "name":"AWS",
  "parent_id":null,
  "children":[
    {
      "id":7,
      "name":"Computing",
      "parent_id":6,
      "children":[
        {
          "id":8,
          "name":"EC2",
          "parent_id":7
        },
        {
          "id":9,
          "name":"Elastic Beanstalk",
          "parent_id":7
        },
        {
          "id":10,
          "name":"Lambda",
          "parent_id":7
        }
      ]
    }
  ]
}

Issue SQL comparison

Example without includes

I put in about 60 data The number of SQL increases according to the data

SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" IS NULL
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 1], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 2], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 3], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 4], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 5], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 6], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 6]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 7], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 7]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 8], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 9], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 10], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 11], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 11]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 12], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id  SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id  SELECT 1 AS one F_i  SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id  SE= $1  [["parent_id", 14]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 15], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 16], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 16]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 17], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["paren  SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["paren  SELECT 1 AS one FROM "ent_id", 19], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 20], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 20]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 21], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 22], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 23], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIM  SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIM  SELECT 1 AS one FROM "technologies" W" = $1  [["parent_id", 24]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 25], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 26], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 27], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 28], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 29], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIM  SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIM  SELECT 1 AS one FROM "technologies" WIMIT $2  [["parent_id", 31], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 31]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 32], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 33], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 34], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 34]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 35], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 36], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 37], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 38], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 39], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 39]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 40], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 41], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 42], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 42]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 43], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 44], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 44]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 45], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 46], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 46]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 47], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 48], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 49], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["paren  SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["paren  SELECT 1 AS one FROM "ent_id", 51], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 51]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 52], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 53], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 54], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 55], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id"   SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id"   SELECT "technologies".* FROM "technolent_id", 56], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 57], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 58], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 59], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 59]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["paren  SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["paren  SELECT 1 AS one FROM "ent_id", 61], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 62], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 63], ["LIMIT", 1]]

Example when solving the N + 1 problem

SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" IS NULL
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" IN ($1, $2, $3, $4, $5, $6)  [["parent_id", 1], ["parent_id", 6], ["parent_id", 46], ["parent_id", 51], ["parent_id", 55], ["parent_id", 59]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29)  [["parent_id", 2], ["parent_id", 3], ["parent_id", 4], ["parent_id", 5], ["parent_id", 7], ["parent_id", 11], ["parent_id", 14], ["parent_id", 16], ["parent_id", 20], ["parent_id", 24], ["parent_id", 31], ["parent_id", 34], ["parent_id", 39], ["parent_id", 42], ["parent_id", 44], ["parent_id", 47], ["parent_id", 48], ["parent_id", 49], ["parent_id", 50], ["parent_id", 52], ["parent_id", 53], ["parent_id", 54], ["parent_id", 56], ["parent_id", 57], ["parent_id", 58], ["parent_id", 60], ["parent_id", 61], ["parent_id", 62], ["parent_id", 63]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28)  [["parent_id", 8], ["parent_id", 9], ["parent_id", 10], ["parent_id", 12], ["parent_id", 13], ["parent_id", 15], ["parent_id", 17], ["parent_id", 18], ["parent_id", 19], ["parent_id", 21], ["parent_id", 22], ["parent_id", 23], ["parent_id", 25], ["parent_id", 26], ["parent_id", 27], ["parent_id", 28], ["parent_id", 29], ["parent_id", 30], ["parent_id", 32], ["parent_id", 33], ["parent_id", 35], ["parent_id", 36], ["parent_id", 37], ["parent_id", 38], ["parent_id", 40], ["parent_id", 41], ["parent_id", 43], ["parent_id", 45]]

Recommended Posts

Erase N + 1 in acts_as_tree of tree structure Gem of Ruby on Rails
Recommendation of Service class in Ruby on Rails
Basic knowledge of Ruby on Rails
(Ruby on Rails6) "Erase" posted content
[Ruby on Rails] Change the save destination of gem refile * Note
[Rails] Addition of Ruby On Rails comment function
Let's summarize "MVC" of Ruby on Rails
Ruby on Rails in Visual Studio Codespaces
[Ruby on Rails] Japanese notation of errors
Explanation of Ruby on rails for beginners ①
[Ruby on rails] Implementation of like function
Beginners create portfolio in Ruby on Rails
A series of flow of table creation → record creation, deletion → table deletion in Ruby on Rails
Implementation of Ruby on Rails login function (Session)
[Ruby on Rails] Until the introduction of RSpec
Rails new in Ruby on Rails ~ Memorandum until deployment 2
Ruby on Rails ~ Basics of MVC and Router ~
[Ruby on Rails] A memorandum of layout templates
Rails new in Ruby on Rails ~ Memorandum until deployment 1
(Ruby on Rails6) Creating data in a table
[Ruby on Rails] How to install Bootstrap in Rails
[Ruby on Rails] Individual display of error messages
Ruby on Rails <2021> Implementation of simple login function (form_with)
[Ruby on Rails] Asynchronous communication of posting function, ajax
[Ruby on Rails] How to write enum in Japanese
[Ruby on Rails Tutorial] Error in the test in Chapter 3
Implementation of Ruby on Rails login function (devise edition)
Docker the development environment of Ruby on Rails project
[Ruby on Rails] Implementation of tagging function/tag filtering function
[Ruby On Rails] How to reset DB in Heroku
Ruby on Rails Elementary
Ruby on Rails basics
[Ruby on Rails] Post image preview function in refile
Solve the N + 1 problem with Ruby on Rails: acts-as-taggable-on
Ruby On Rails Association
Explanation of Ruby on rails for beginners ⑥ ~ Creation of validation ~
Explanation of Ruby on rails for beginners ② ~ Creating links ~
Arrange posts in order of likes on Rails (ranking)
Try using the query attribute of Ruby on Rails
Explanation of Ruby on rails for beginners ⑦ ~ Flash implementation ~
What I was interested in in Progate's Ruby on Rails course [Each statement of error message]
[Ruby on Rails] I want to get the URL of the image saved in Active Storage
Difficulties in building a Ruby on Rails environment (Windows 10) (SQLite3)
Definitely useful! Debug code for development in Ruby on Rails
Ruby on Rails for beginners! !! Summary of new posting functions
[Procedure 1 for beginners] Ruby on Rails: Construction of development environment
[Ruby on Rails] Elimination of Fat Controller-First, logic to model-
(Ruby on Rails6) Display of the database that got the id of the database
Delete all the contents of the list page [Ruby on Rails]
A note about the seed function of Ruby on Rails
[Apple login] Sign in with Apple implementation procedure (Ruby on Rails)
How to display a graph in Ruby on Rails (LazyHighChart)
Explanation of Ruby on rails for beginners ③ ~ Creating a database ~
Apply CSS to a specific View in Ruby on Rails
[Ruby on Rails] Infinite scrolling using gem kaminari and jscroll
Ruby on rails learning record -2020.10.03
Portfolio creation Ruby on Rails
Ruby on rails learning record -2020.10.04
[Ruby on Rails] Debug (binding.pry)
Ruby on rails learning record -2020.10.05
Ruby on rails learning record -2020.10.09