[SOLVED] Rails, query with Active record does not take into account negative where condition

Issue

This Content is from Stack Overflow. Question asked by David Geismar

I have the following data model :

user has_many organization_users

user has_many registrations

a user has a column type

a registration has columns learning_item_type and learning_item_id

The goal of the query is to retrieve all users from a specific organization that dont have registrations to a specific learning item (combinaison of learning_item_type and learning_item_id), it must include users from the organizations that don’t have registrations at all (left join)
I came up with this with active record query :

User
  .joins(:organizations_users)
  .left_joins(:registrations)
  .where(
    type: 'Collaborator',
    'organizations_users.organization_id': organization.id
  )
  .where.not(
    'registrations.learning_item_id': learning_item.id,
    'registrations.learning_item_type': learning_item.class.to_s
  ).distinct

which in raw sql looks like :

"SELECT DISTINCT "users".* FROM "users" INNER JOIN "organizations_users" ON "organizations_users"."user_id" = "users"."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 NOT ("registrations"."learning_item_id" = 10164 AND "registrations"."learning_item_type" = 'Session')"

I can’t figure out what’s wrong with this query but it keeps returning users who actually have a registration with learning_item_id = 10164 and learning_item_type ‘Session’.

Why is that negative NOT criteria not taken into account here ?



Solution

User.left_joins(:registrations)

After the join you get something like this:

users.idregistrations.learning_item_id
110164
110165
210166

Then filter out learning item 10164:

User.left_joins(:registrations)
    .where.not("registrations.learning_item_id": 10164)

Result is:

users.idregistrations.learning_item_id
110165
210166

The returned sql result is correct, but you still get User#1 that is registered to LearningItem#10164.

What you need is to filter out the user:

User.where.not(
  id: User.joins(:registrations).where("registrations.learning_item_id": 10164)
)
users.idregistrations.learning_item_id
210166


This Question was asked in StackOverflow by David Geismar and Answered by Alex It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?