Rails -多对多过滤

Question

我在'Event'和'Member'之间有一个多对多关系。我正在尝试过滤掉所有不属于某个成员的事件。

我可以做相反的事情，我可以选择属于特定事件的所有事件。我是这样做的:

class Event < ApplicationRecord
has_and_belongs_to_many :members
scope :filter_by_member, -> (member) { joins(:members).where( members: { id: member.id } ) }
end

class Member < ApplicationRecord
has_and_belongs_to_many :events
end

我试图通过将where更改为where.not来获得反转行为(所有不属于成员的事件)，然而;这只是选择事件表中的所有记录。

这里是我试图得到所有不属于成员的事件，在rails控制台，切换where到where.not后。但是，正确的输出应该是最后一行中选择的事件2和3(因为事件1属于sample_member);

irb(main):001:0> sample_member = Member.find(2)
Member Load (0.3ms)  SELECT "members".* FROM "members" WHERE "members"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
=> #<Member id: 2, uid: nil, name: "John", created_at: "2021-10-23 14:30:38.434442000 -0500", updated_at: "2021-10-23 14:30:38.434442000 -0500", isAdmin: nil, email: nil>
irb(main):002:0> sample_member.events
Event Load (0.4ms)  SELECT "events".* FROM "events" INNER JOIN "events_members" ON "events"."id" = "events_members"."event_id" WHERE "events_members"."member_id" = $1  [["member_id", 2]]
=> 
[#<Event:0x00007f02f0aeb4e8
id: 1,
date: nil,
description: "Please come",
isMandatory: nil,
location: nil,
admin_id: nil,
created_at: Sat, 23 Oct 2021 14:27:32.966994000 CDT -05:00,
updated_at: Sat, 23 Oct 2021 14:27:32.966994000 CDT -05:00,
time: nil,
datetime: nil,
name: "Fun event">]
irb(main):003:0> Event.filter_by_member sample_member
Event Load (0.3ms)  SELECT "events".* FROM "events" INNER JOIN "events_members" ON "events_members"."event_id" = "events"."id" INNER JOIN "members" ON "members"."id" = "events_members"."member_id" WHERE "members"."id" != $1  [["id", 2]]
=> 
[#<Event:0x00005651319bf3d8
id: 1,
date: nil,
description: "Please come",
isMandatory: nil,
location: nil,
admin_id: nil,
created_at: Sat, 23 Oct 2021 14:27:32.966994000 CDT -05:00,
updated_at: Sat, 23 Oct 2021 14:27:32.966994000 CDT -05:00,
time: nil,
datetime: nil,
name: "Fun event">,
#<Event:0x00005651319bf270
id: 2,
date: nil,
description: "Important",
isMandatory: nil,
location: nil,
admin_id: nil,
created_at: Sat, 23 Oct 2021 14:29:39.380136000 CDT -05:00,
updated_at: Sat, 23 Oct 2021 14:29:39.380136000 CDT -05:00,
time: nil,
datetime: nil,
name: "Conference">,
#<Event:0x00005651319bf158
id: 3,
date: nil,
description: "this is orientation",
isMandatory: false,
location: "",
admin_id: nil,
created_at: Sat, 23 Oct 2021 14:39:13.186162000 CDT -05:00,
updated_at: Sat, 23 Oct 2021 14:39:13.186162000 CDT -05:00,
time: nil,
datetime: nil,
name: "orientation">]

什么线索吗?

Answer 1

最简单的方法是使用WHERE events.id NOT IN (...)子查询:

Event.where.not(
id: member.events
)