通过连接表和parent_id实现ActiveRecord查询

Question

我正在越来越深入地研究RoRails，我被困在我们给我的数据面前，让我在这个可爱的框架上重现。让我先向您展示我的表和模型关联。

  create_table "skills", force: :cascade do |t|
    t.string "name"
    t.bigint "parent_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["parent_id"], name: "index_skills_on_parent_id"
  end

  create_table "skills_users", id: false, force: :cascade do |t|
    t.bigint "skill_id", null: false
    t.bigint "user_id", null: false
    t.index ["skill_id", "user_id"], name: "index_skills_users_on_skill_id_and_user_id"
  end

  create_table "users", force: :cascade do |t|
    t.integer "points"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

和型号：

class Skill < ApplicationRecord
  validates :name, presence: true
  has_many :children, class_name: "Skill", foreign_key: :parent_id
  belongs_to :parent, class_name: "Skill", foreign_key: :parent_id, optional: true
  has_and_belongs_to_many :users
end

class User < ApplicationRecord
  validates :points, presence: true
  has_and_belongs_to_many :skills
end

目标是对技能进行分类，获取子级技能，并计算用户在每个父技能中的点数。下面是一个数据示例

SKILLS
+-----------------------+
|ID|NAME      |PARENT_ID|
+-----------------------+
|1 |Football  |         |
+-----------------------+
|2 |Basketball|         |
+-----------------------+
|3 |Foot      |1        |
+-----------------------+
|4 |Basket    |2        |
+-----------------------+
|5 |Soccer    |1        |
+-----------------------+

SKILLS_USERS
+-------------------+
|ID|SKILL_ID|USER_ID|
+-------------------+
|1 |1       |1      | 
+-------------------+
|2 |1       |2      | 
+-------------------+
|3 |3       |3      | 
+-------------------+
|4 |2       |4      | 
+-------------------+
|5 |5       |5      |
+-------------------+

USERS
+---------+
|ID|POINTS|
+---------+
|1 |100   |
+---------+
|2 |200   |
+---------+
|3 |100   |
+---------+
|4 |50    |
+---------+
|5 |10    |
+---------+

预期的请求应如下所示：

+--------------------------------+
|ID|NAME      |POINTS|USERS_COUNT|
+--------------------------------+
|1 |Football  |410   |4          |
+--------------------------------+
|2 |Basketball|50    |1          |
+--------------------------------+

我首先尝试用这个查询用纯sql来回答这个问题：

SELECT id , Name , count ( points)  as POINTS , count (USER_ID ) as USERS_COUNT 
FROM SKILLS 
INNER JOIN SKILLS as SK ON id = SK.parent_id 
INNER JOIN SKILLS_USERS AS su ON su.skill_id = id 
INNER JOIN USERS AS User ON SU.USER_ID = User.id

但似乎我在某些方面错了。

我认为ActiveRecord的方式很柔韧，ruby很神奇。

Answer 1

如果预期的目的是记录每个用户对给定技能的得分，那么您需要将其放在join表中，而不是users表中。

 create_table "skills", force: :cascade do |t|
    t.string "name"
    t.bigint "parent_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["parent_id"], name: "index_skills_on_parent_id"
  end

  create_table "user_skills", force: :cascade do |t|
    t.bigint "skill_id", null: false
    t.bigint "user_id", null: false
    t.integer "points"
    t.index ["skill_id", "user_id"], name: "index_skills_users_on_skill_id_and_user_id"
  end

  create_table "users", force: :cascade do |t|
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

并且您希望设置与has_many through:而不是has_and_belongs_to_many的关联，这将不允许您直接查询连接表或访问该points列。

class Skill < ApplicationRecord
  has_many :user_skills
  has_many :users, through: :user_skills
end

class UserSkill < ApplicationRecord
  belongs_to :user
  belongs_to :skill
end

class User < ApplicationRecord
  has_many :user_skills
  has_many :users, through: :user_skills
end

您的查询也比所需的复杂得多，并且仍然没有命中目标。您可以通过查询技能表并加入user_skills来获得所需的结果。

SELECT skills.id, skills.name, 
  SUM(user_skills.points) AS points, 
  count(user_skills.user_id) AS user_count 
FROM "skills" 
INNER JOIN "user_skills" ON "user_skills"."skill_id" = "skills"."id" 
GROUP BY skills.id

我们可以在ActiveRecord中这样做：

Skill.joins(:user_skills)
     .select('skills.id, skills.name, SUM(user_skills.points) AS points, COUNT(user_skills.user_id) AS user_count')
     .group('skills.id')

这将返回Skill记录的ActiveRecord::Relation，以及附加的.points和.user_count属性。