Rails - filter many to many-CodePudding

I have a many-to-many relationship between 'Event' and 'Member'. I am currently trying to filter out all events that don't belong to a certain member.

I'm able to do the opposite, where I can select all events that belong to a particular event. This is how I did it:

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

I tried to get the inverted behavior (all events that don't belong to member) by changing where to where.not, however; this was simply selecting all records in the events table.

Here is me trying to get all the events not belonging to a member, in rails console, after switching where to where.not. The correct output should be events 2 and 3 being selected in the last line (as event 1 belongs to sample_member), however; all 3 are being selected.

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">]

Any clues?

CodePudding user response：

The simplest way to do this is with a WHERE events.id NOT IN (...) subquery:

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