I am trying to retrieve all Collaborators (Collaborator being a subclass of User) within a specific Organization who are not registered to a specific learning_item.
My data model looks like this :
Collaborator has_many registrations
Collaborator has_many learning_items through registrations
Collaborator has_many organizations_users
Collaborator has_many organizations through organizations_users
LearningItem is a polymorphic object that can be a Program, a Group or a Session.
In my Collaborator model I have scopes :
scope :without_registrations, -> { where.missing(:registrations) }
scope :without_registration_to_learning_item, lambda { |learning_item|
where.not(
'registrations.learning_item_id': learning_item.id,
'registrations.learning_item_type': learning_item.class.to_s
)
}
Finally to achieve my query. Here's what Ive tried :
organization
.collaborators
.without_registrations
.or(
organization
.collaborators
.without_registration_to_learning_item(learning_item)
).distinct
The SQL query generated is the following :
"SELECT DISTINCT \"users\".* FROM \"users\" INNER JOIN \"organizations_users\" ON \"users\".\"id\" = \"organizations_users\".\"user_id\" LEFT OUTER JOIN \"registrations\" ON \"registrations\".\"account_id\" = 28 AND \"registrations\".\"user_id\" = \"users\".\"id\" WHERE \"users\".\"account_id\" = 28 AND \"users\".\"type\" = 'Collaborator' AND \"organizations_users\".\"organization_id\" = 1 AND (\"registrations\".\"id\" IS NULL OR NOT (\"registrations\".\"learning_item_id\" = 10164 AND \"registrations\".\"learning_item_type\" = 'Session'))"
This query keeps on returning collaborators that are already registered to the specific learning_item.
Is there something wrong in the logic of my query ? How can I modify it so it returns the correct data.