sql - 多个 has_many 关系 Rails SQL 查询

Question

我有一个教练和体育通过专业水平联系起来。我的教练也有类(class)。

鉴于用户对某项运动感兴趣，我希望获得所有具有一定水平专业知识的教练的类(class)。

获取用户感兴趣的具有特定体育专业知识的教练提供的类(class)的有效查询是什么？

尝试

Coach.includes(:expertises => :sport).
      where(["expertises.skill_level > ?", 0]).
      where(sports: {id: user.sports.map(&:id)})

尝试

Course.joins(coach: {expertises: :sport})
      .where(
           expertises: {skill_level: 1}, 
           sports: {id: user.sports.map(&:id)}
       )

原始尝试

coaches = []
qualified_coaches = []
courses = []
user_sports = User.sports
coach_quality_points_needed = user_sports.count
coach_quality_points

# get all the coaches who have an expertise in one of the user's sports
user_sports.each do |sport|
   coaches << Coach.joins(:expertises).where("expertises.sport_id = ?", sport.id)
end

# make list unique
coaches = coaches.uniq

# loop through the coaches
# add a point if they are skilled enough in each sport
coaches.each do |coach|
  coach_quality_points = 0
  user_sports.each do |user_sport|
    if coach.expertises.where(sport_id: user_sport.id).first.skill_level > 0
      coach_quality_points++
    end
  end

  # if the coach gets the necessary points then add to qualified list
  if coach_quality_points == coach_quality_points_needed
   qualified_coaches << coach 
end

# get all the courses of the qualified coaches
qualified_coaches.each do |coach|
  courses << Course.where(coach_id: coach.id)
end

这些是我的关系。

用户

class User < ActiveRecord::Base
  has_many_and_belongs_to_many :sports 
end

create_table "users_sports", id: false, force: true do |t|
    t.integer "sport_id", null: false
    t.integer "user_id",  null: false
end

教练

class Coach < ActiveRecord::Base
  has_many :expertises 
  has_many :sports, through: :expertises
  has_many :courses
end

专长

class Expertise < ActiveRecord::Base
  belongs_to :coach
  belongs_to :sport
end

create_table "expertises", force: true do |t|
    t.integer  "skill_level"
    t.integer  "coach_id"
    t.integer  "sport_id"
    t.datetime "created_at"
    t.datetime "updated_at"
end

运动

class Sport < ActiveRecord::Base
  has_many :expertises 
  has_many :coaches, through: :expertises
end

类(class)

class Course < ActiveRecord::Base
  belongs_to :coach
end

Answer 1

我认为您的 ActiveRecord 查询将如下所示:

Course.joins(coach: {expertises: :sport}).where("expertises.skill_level >= ?", level).where(sports: {id: user.sports.map &:id})

其中 level 是您想要的专业技能水平，user 是相关用户。让我知道进展如何!