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.
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
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
Let parent_id (or any key) be the parent key of the tree structure.
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
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.
# model
class Technology < ApplicationRecord
acts_as_tree
def dig
if children.empty?
self
else
children.map(&:dig)
end
end
end
# 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
}
]
}
]
}
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]]
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