Find records that assoicated records do not belong to a specific record
On my system, I have the 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 one @event
. How can I find all workers
that have NOs worker_memberships
belonging to this @event
?
source to share
This is pretty much a synthesis of both of the other answers.
First: stick has_many through
as @TheChamp suggests. You may already be using it, you just forgot to write it, otherwise it just won't work. Well, you've been warned.
I usually try to avoid raw SQL queries in my queries. The above hint select
gives a working solution, but does some unnecessary things like join
when there is no practical need. Thus, avoid poking the association. Not this time.
This is why I prefer has_many through
- has_and_belongs_to_many
in many, many associations: we can query the connection model itself without the raw SQL:
WorkerMembership.select(:worker_id).where(event: @event)
This is not a result yet, but we worker_id
don't need a list . Then we just move that request to "give me everyone but these guys":
Worker.where.not(id: <...> )
So the final request is:
Worker.where.not(id: WorkerMembership.select(:worker_id).where(event: @event) )
And it issues one request ( @event
with id
equal 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 hint at mysql2
explain
. SQLite explain
is terrible! My solution, if I read the result correctly explain
, is as effective as @apneadiving's.
@TheChamp also provided performance costs for all response requests. Check out the comments for a comparison.
source to share
Try the following:
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 took the table and column names from the legend)
source to share
Since you want to establish many, many relationships between Worker
and Event
, I would suggest that you use through association .
Your resulting models will 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 simply call @event.workers
to have all workers associated with the event.
To find all workers that don't belong @event
, you can use:
# get all the id 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)
One line
Worker.where.not(:id => @event.workers.select(:id))
source to share