Rails query: Attempting to find errors in database-CodePudding

I have a model, CheckIn, that belongs to both Client and Program in such a way that both client_id and program_id are fields. Program also belongs to Client. I'm attempting to locate all check_ins where the Program that they belong to, belongs to a different client.

I've noticed this mismatch on a few records and I'm trying to determine how frequently this occurred.

class CheckIn < ApplicationRecord
    belongs_to :program
    belongs_to :client
end

class Program < ApplicationRecord
    belongs_to :client
end

class Client < ApplicationRecord
    has_many :check_ins
    has_many :programs
end

An example of a record with a mismatch that I would like to query for would be something like this because the program it belongs to does not belong to the same client:

#<CheckIn:0x00000001149ef9e8> {
  :id => 336964,
  :created_at => Wed, 29 Jun 2022 17:06:19.567000000 EDT -04:00,
  :updated_at => Wed, 29 Jun 2022 17:06:30.280633000 EDT -04:00,
  :client_id => 45290,
  :program_id => 26266,
}

#<Program:0x00000001141e4d70
  id: 26266,
  client_id: 46220, # should match CheckIn (45290)
  created_at: Tue, 18 Oct 2022 07:19:13.735740000 EDT -04:00,
  updated_at: Tue, 18 Oct 2022 07:19:13.735740000 EDT -04:00
>

How might I write that query? I tried the following but don't know how to access the CheckIn's client_id:

CheckIn.joins(:program).where.not(programs: {client_id: CLIENT_ID_HERE})

CodePudding user response：

Try:

CheckIn.joins(:program).where('programs.client_id <> check_ins.client_id')