我正在制作一个应用程序,用户可以在其中完成任务。这些任务具有关联Requirements
。User
有一个关联Qualifications
两者通过共享QualificationCategory
连接
Task
有很多要求,User
有很多Qualifications
。
需求的结构如下:
Requirement
- ID
- qualification_category_id
- task_type_id
- points_required
资格的结构如下:
Qualification:
- ID
- qualification_category_id
- user_id
- points
问题是这样的:
对于用户,我需要找到允许他完成的所有任务。所以我想从数据库中选择task_type.requirements
在user.qualifications
内完全匹配的所有任务
因此,对于每个任务,检查要求是否与基于qualification_category的任何用户资格相匹配,对于每个要求,检查相应的用户资格点是否高于或等于所需的分数。
模型
class Requirement < ApplicationRecord
belongs_to :qualification_category
belongs_to :task_type, inverse_of: :requirements
end
class Task < ApplicationRecord
belongs_to :task_type
belongs_to :data_sourceable, polymorphic: true, optional: true
belongs_to :user_id, optional: true
belongs_to :solution, class_name: 'Hypothesis', optional: true
belongs_to :payment_period, optional: true
has_many :worker_groups, through: :task_type
has_many :requirements, through: :task_type
end
class User < ApplicationRecord
# Include default devise modules. Others available are:
# :confirmable, :lockable, :timeoutable and :omniauthable
devise :database_authenticatable, :timeoutable, :confirmable, :lastseenable,
:recoverable, :rememberable, :trackable, :validatable, :registerable
has_and_belongs_to_many :roles
has_and_belongs_to_many :worker_groups
has_many :payment_periods
has_many :qualifications, inverse_of: :user
has_many :qualification_categories, through: :qualifications
end
class Qualification < ApplicationRecord
belongs_to :qualification_category
belongs_to :user, inverse_of: :qualifications
end
如何编写MySQL查询或ARel语句来完成此操作。该应用程序将包含数百万个任务,因此在 ruby 中执行此操作是不可取的。
DB小提琴:https://www.db-fiddle.com/f/2jBx1gQhzqn1hYS5xD82n/0
要返回特定user
有资格获得的所有task
(基于匹配单个限定条件),MySQL 查询如下所示:
SELECT t.id
FROM task t
JOIN task_type tt
ON tt.id = t.task_type_id
JOIN requirement r
ON r.task_type_id = tt.id
JOIN qualification q
ON q.qualification_category_id = r.qualification_category_id
AND q.points >= r.points_required
JOIN user u
ON u.id = q.user_id
WHERE u.id = ?
这可能会返回"重复";因此我们可以添加一个 GROUP BY 子句,或者将连接操作更改为 EXISTS 谓词。
为了仅匹配用户资格满足所有要求的任务,我们需要一些不同的东西:
SELECT t.id
FROM task t
JOIN task_type tt
ON tt.id = t.task_type_id
WHERE EXISTS
( SELECT 1
FROM requirement r
JOIN qualification q
ON q.qualification_category_id = r.qualification_category_id
AND q.points >= r.points_required
WHERE r.task_type_id = tt.id
AND q.user_id = ? -- specific user
)
AND NOT EXISTS
( SELECT 1
FROM requirement r
LEFT
JOIN qualification q
ON q.qualification_category_id = r.qualification_category_id
AND q.user_id = ? -- specific user
WHERE r.task_type_id = tt.id
AND ( q.user_id IS NULL OR q.points < r.points_required )
)
EXISTS
位检查用户是否至少具有一个满足要求的资格。
NOT EXISTS
位检查用户没有匹配qualification
的任务类别是否有任何requirement
。
再考虑一下,我认为EXISTS
部分可以省略,我们只需要检查NOT EXISTS
......是否有任何requirement
没有被qualification
统计.
SELECT t.id
FROM task t
JOIN task_type tt
ON tt.id = t.task_type_id
WHERE NOT EXISTS
( SELECT 1
FROM requirement r
LEFT
JOIN qualification q
ON q.qualification_category_id = r.qualification_category_id
AND q.user_id = ? -- specific user
WHERE r.task_type_id = tt.id
AND ( q.user_id IS NULL OR q.points < r.points_required )
)
(这假定qualification.points
和requirement.points_required
为非空,因此不等式比较的计算结果将为 TRUE。 如果我们有可能出现 NULL 值,我们将需要适当地处理这些值。
为了补充斯宾塞的答案,对于那些希望通过示波器和ARel将其纳入他们的轨道项目的人。
scope :qualified_for, ->(user) {
task_types = TaskType.arel_table
requirements = Requirement.arel_table
qualifications = Qualification.arel_table
requirements_conditions = requirements[:task_type_id].eq(task_types[:id])
.and(
qualifications[:user_id].eq(nil).or(qualifications[:points].lt(requirements[:points_required]))
)
requirement_query = Requirement.select(1).joins("LEFT
JOIN qualifications
ON qualifications.qualification_category_id = requirements.qualification_category_id
AND qualifications.user_id = #{user.id}").where(requirements_conditions).exists.not
joins(:task_type).where(requirement_query)
}