CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
P.F.
P.F.

Reputation: 65

ActiveRecord Query for Present belongs_to ID but belongs_to Object is Nil

I wasn't really sure how to title this because everything I have searched for just gives me some form of the following query: Telephone.where(user_id: nil) which is not what I'm looking for

I have two models:

Class Telephone < ApplicationRecord
 belongs_to :user, optional: true
end

Class User < ApplicationRecord
 has_one :telephone, dependent: :nullify
end

There was a bug in the code that wasn't always nullifying the user_id on a telephone when a user object was destroyed. I need to do a data clean up and set the user_id to nil for all affected telephones. I wrote the following bit of code to update the ~80 telephones, but there are >400000 records that get returned in the initial query so it takes a long time to loop through:

telephones = Telephone.where.not(user_id: nil)

telephones.each do |telephone|
  return unless telephone.user.nil?

  telephone.user_id = nil
  telephone.save
end

What is an Active Record query I could write to return all telephones with a present user_id but the user object is nil?

Upvotes: 0

Views: 693

Answers (2)

faraquet
faraquet

Reputation: 21

You have to use left_joins on user association:

Telephone.left_joins(:user).where.not(user: nil).where("users.id": nil)

what is converted to a SQL-query

SELECT "telephones".* FROM "telephones" LEFT OUTER JOIN "users" ON "users"."id" = "telephones"."user_id" WHERE "telephones"."user_id" IS NOT NULL AND "users"."id" IS NULL

Upvotes: 1

Gowtham
Gowtham

Reputation: 652

Following should get you telephones with user_id present but corresponding user object is nil.

Telephone.left_outer_joins(:user).where("users.id is NULL AND telephones.user_id is NOT NULL")

Upvotes: 3

Related Questions