ruby-on-rails - 在 ActiveRecord 中

Question

几天来我一直在努力寻找解决我的问题的方法，所以我转向社区，希望我没有遗漏一些明显的东西。

我在 rails 中有 2 个模型:

class Room
  has_many :accesses
end

class Access
  belongs_to :accessor, polymorphic: true
end

访问者可以是两种类型:个人或团队

我正在尝试找到最有效的方法来查找用户有权访问但任何团队都无法访问的房间。

我试过:

Room.joins(:accesses).where(accesses: {accessor: Person.find(1234)}).where.not(accesses: {accessor_type: Team'})

但这会返回人们有权访问的房间，它不会过滤掉团队和人员有权访问的房间。

我认为 having 子句是可行的方法，它会计算团队访问房间的次数，并保留团队访问权限为 0 的房间。尽管我所有的尝试都失败了。

我很想听听任何建议。

Answer 1

左连接

我不使用 HAVING，这需要我们添加一个 GROUP BY，而是从一个 LEFT JOIN 和一个 开始>哪里。

您可以通过在 "Team" accessor_type 上左连接到 room_accesses 表来做到这一点。我们进行左连接是因为我们要将此连接的范围限定为仅 团队访问权限，并且仅选择不存在此类访问权限的行。内部联接根本不会返回这些行。我们需要使用表别名，因为我们已经在使用 room_accesses 表来加入您要查找的人。

我们不妨承认 Rails 在这个查询抽象级别上并不出色，所以让我们为第一个解决方案构建原始 SQL 片段:

person = Person.find(1234)
person.rooms.joins(
  "LEFT JOIN room_accesses team_accesses
    ON team_accesses.room_id = rooms.id
    AND team_accesses.accessor_type = 'Team'"
).where("team_accesses.id IS NULL")

这会为 SQLite 生成

SELECT "rooms".* FROM "rooms"
  INNER JOIN "room_accesses"
    ON "rooms"."id" = "room_accesses"."room_id"
  LEFT JOIN room_accesses team_accesses
    ON team_accesses.room_id = rooms.id
    AND team_accesses.accessor_type = 'Team'
  WHERE "room_accesses"."accessor_id" = 1
    AND "room_accesses"."accessor_type" = 'Person'
    AND (team_accesses.id IS NULL)

有

您可以使用 HAVING 执行此操作，方法是使用 team_accesses 别名再次加入 room_accesses，按 rooms.id 分组(因为我们希望每个房间最多有一个记录)，并选择团队访问次数为零的组:

person.rooms.joins(
  "LEFT JOIN room_accesses team_accesses
    ON team_accesses.room_id = rooms.id
    AND team_accesses.accessor_type = 'Team'"
).group("rooms.id").having("COUNT(team_accesses.id) = 0")

生成:

SELECT "rooms".* FROM "rooms"
  INNER JOIN "room_accesses"
    ON "rooms"."id" = "room_accesses"."room_id"
  LEFT JOIN room_accesses team_accesses
    ON team_accesses.room_id = rooms.id
    AND team_accesses.accessor_type = 'Team'
  WHERE "room_accesses"."accessor_id" = 1
    AND "room_accesses"."accessor_type" = 'Person'
  GROUP BY rooms.id
  HAVING (COUNT(team_accesses.id) = 0)

使用关联而不是原始 SQL

在 Rails 中，您可以通过定义作用域关联来实现这一点:

class Room < ApplicationRecord
  has_many :room_accesses
  has_many :team_accesses, ->{ where accessor_type: "Team" }, class_name: "RoomAccess"
end

假设您使用的是最新版本的 ActiveRecord，这允许您这样做

person.rooms.left_joins(:team_accesses)

但是，用于此左连接的表名是 “team_accesses_rooms”，在这种简单情况下这是可以预测的，但据我所知这不是公共(public) API 的一部分，并且如果其他连接可能会更改在同一个查询中使用。不过，如果您胆子大:

person.rooms.left_joins(:team_accesses).where(team_accesses_rooms: {id: nil})

坦率地说，我不会推荐这种方法，因为您依赖于您无法控制的表别名，而且它的来源并不明显。使用原始 SQL，您可以控制它，而且它的来源一目了然。