mysql - 如何根据关联的全匹配查找记录

Question

我正在制作一个应用程序，用户可以在其中完成任务。这些任务具有关联Requirements。 用户有一个关联资格

两者通过共享的QualificationCategory连接

任务有许多要求，用户有许多资格。

需求的结构如下:

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​​ 中这样做是不可取的。

数据库 fiddle :https://www.db-fiddle.com/f/2jBx1gQhzqn1hYS5xD82n/0

Answer 1

返回全部task特定的user符合(基于匹配单个资格)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 bit 检查用户是否至少拥有一项满足要求的资格。

NOT EXISTS位检查是否有任何 requirement对于用户没有匹配的任务类别 qualification .

<小时/>

再多考虑一下，我认为 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值，我们需要适本地处理这些值。)