Wsrtjtyk

I'm studying deeper and deeper RoRails and i am stuck in front of datas we gave me to reproduce on this lovely framework.
Let me show you first my tables and the models associates.

  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

And models:

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

The goal is to class Skills, fetch Skills children and count Users'points in each parent Skill. Here is an example of data

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    |

+---------+

The request expected should look like this :

+--------------------------------+

|ID|NAME      |POINTS|USERS_COUNT|

+--------------------------------+

|1 |Football  |410   |4          |

+--------------------------------+

|2 |Basketball|50    |1          |

+--------------------------------+

I'm trying first to answer it in pure sql with this query:

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

But it seems i'm wrong somewhere.

I think ActiveRecord way is much plaisant, ruby is magic. What is the ActiveRecord way to do this kind of request thgrouht to a join table and where we select only parent Skills ?

If the intended purpose is to record the score of each user for a given skill then you need to place it on the join table and not the users table.

 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

And you want to setup the associations with has_many through: and not has_and_belongs_to_many which does not allow you to query the join table directly or access that points column.

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

You query is also much more complex than needed and still misses the mark. You can get the desired result by querying the skills table and joining 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

Which we can do in ActiveRecord with:

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')

This will return an ActiveRecord::Relation of Skill records with additional .points and .user_count attributes.