ActiveRecord/SQL Retrieve all User not registered to an item

Viewed 65

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.

0 Answers
Related