CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
glizda101
glizda101

Reputation: 113

Find records which assoicated records do not belong to certain record

In my system I have a following structure:

class Worker
  has_many :worker_memberships
end

class WorkerMembership
  belongs_to :worker
  belongs_to :event
end

class Event
  has_many :worker_memberships
end

Imagine I have a certain @event. How can I find all workers that have NO worker_memberships belonging to this @event?

Upvotes: 3

Views: 581

Answers (3)

D-side
D-side

Reputation: 9485

This is pretty much synthesis of both other answers.

First: stick to has_many through as @TheChamp suggests. You're probably using it already, just forgot to write it, otherwise it just wouldn't work. Well, you've been warned.

I generally do my best to avoid raw SQL in my queries whatsoever. The hint about select I provided above produces a working solution, but does some unneessary stuff, such as join when there's no practical need for it. So, let's avoid poking an association. Not this time.

Here comes the reason why I prefer has_many through to has_and_belongs_to_many in many-to-many associations: we can query the join model itself without raw SQL:

WorkerMembership.select(:worker_id).where(event: @event)

It's not the result yet, but it gets us the list of worker_ids we don't want. Then we just wrap this query into a "give me all but these guys":

Worker.where.not(id: <...> )

So the final query is:

Worker.where.not(id: WorkerMembership.select(:worker_id).where(event: @event) )

And it outputs a single query (on @event with id equal to 1):

SELECT `workers`.* FROM `workers` WHERE (`workers`.`id` NOT IN (SELECT `worker_memberships`.`worker_id` FROM `worker_memberships` WHERE `worker_memberships`.`event_id` = 1))

I also give credit to @apneadiving for his solution and a hint about mysql2's explain. SQLite's explain is horrible! My solution, if I read the explain's result correctly, is as performant as @apneadiving's.

@TheChamp also provided performance costs for all answers' queries. Check out the comments for a comparison.

Upvotes: 2

apneadiving
apneadiving

Reputation: 115531

Try this:

Worker.where(WorkerMembership.where("workers.id = worker_memberships.worker_id").where("worker_memberships.event_i = ?", @event.id).exists.not)

Or shorter and reusable:

class WorkerMembership
  belongs_to :worker
  belongs_to :event

  scope :event, ->(event){ where(event_id: event.id) }
end

 Worker.where(WorkerMembership.where("workers.id = worker_memberships.worker_id").event(@event.id).exists.not)

(I assumed table and column names from conventions)

Upvotes: 1

davegson
davegson

Reputation: 8331

Since you want to set up a many to many relationship between Worker and Event, I'd suggest you use the through association.

Your resulting models would be.

class Worker
  has_many :worker_memberships
  has_many :events, :through => :worker_memberships
end

class WorkerMembership
  belongs_to :worker
  belongs_to :event
end

class Event
  has_many :worker_memberships
  has_many :workers, :through => :worker_memberships
end

Now you can just call @event.workers to get all the workers associated to the event.

To find all workers that don't belong to the @event you can use:

# get all the id's of workers associated to the event
@worker_ids = @event.workers.select(:id)

# get all workers except the ones belonging to the event
Worker.where.not(:id => @worker_ids)

The one-liner

Worker.where.not(:id => @event.workers.select(:id))

Upvotes: 1

Related Questions