mysql - has_and_belongs_to_many 权限过滤

Question

我需要查找两个模型之间的记录。

class Report
  has_and_belongs_to_many :groups
end

class Group
  has_and_belongs_to_many :reports
end

和 join_table - reports_groups

我有一个可以访问的组 ID 数组，例如 [1, 2]。我需要查找包含我有权访问的群组的报告，仅限他们。

If report has groups list like: [1], [2], [1,2] it is OK.
If report has - [1,2,3] - skip it.
If - [3,4] - skip it to.

我只需要允许我的组的报告。

Answer 1

仔细检查连接表的名称。

Report.where(
  'NOT EXISTS(SELECT * FROM reports_groups WHERE report_id = reports.id AND group_id IN (?))',
  [1, 2],
)