[Ruby] Erase N+1 with acts_as_tree of Ruby on Rails tree structure Gem

8 minute read

Handheld operating environment

Of course, it is not possible to put the code that is in service, so I have written a similar code in my environment and verified the operation.

  • ruby 2.7.1p83
  • Rails 6.0.3.2
  • acts_as_tree (2.9.1)

Wood structure

Looking at old code, there are many anti-patterns. This time as well, the neighbor list, which is one of the anti-patterns, was used. In addition, the N+1 problem was left unaddressed, and it was decided to address it due to poor performance. This time, there were only two layers in the specification, so it seems that there is not much 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

Assumption

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 grows according to the acts_at_tree added to the model, so include it and associate it with it so that it will be loaded with the minimum number of queries in advance.

Resolution code

# model
class Technology <ApplicationRecord
  acts_as_tree

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

I will add ### includes.

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

response

When you extract the code of two layers, it looks 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 entered about 60 data The number of SQLs 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]]

N+1問題解消時の例

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]]