How to filter a model on tha basis of Column Format in Rails Console

Question

I have been struggling with a Client request to filter out A model(Candidate) on the basis of Column Format

The issue I am facing is that column inputs either SSN or EIN. The format for SSN is (xxx-xx-xxxx) The format for EIN is (xx-xxxxxxx)

My candidates Table contains the field ssn_or_ein , which takes either one of these 2.

For ex: candidate111.ssn_or_ein => 111-11-1111 candidate222.ssn_or_ein => 22-2222222

I have tried fetching all the 4000 accounts, but i suppose that's not how a developer's approach should be.

I am still learning Rails and any tip would be really helpful.

Answer 1

You can do this with a like query. Put it in a scope so it's easily available.

class Candidate < ApplicationRecord
  scope with_ein -> { where( "ssn_or_ein like ?", "__-_______" }
  scope with_ssn -> { where( "ssn_or_ein like ?", "___-__-____" }
end

However, this can get slow if ssn_or_ein is not properly indexed.

---

Consider storing them in two different columns. This makes validation and querying simpler. Bring them together only when you just need a TIN - Taxpayer Information Number.

class Candidate < ApplicationRecord
  scope with_ein -> { where.not( ein: nil ) }
  scope with_ssn -> { where.not( ssn: nil ) }

  EIN_RE = %r{^\d{2}-\d{7}$}
  SSN_RE = %r{^\d{3}-\d{2}-\d{4}$}
  validates :ein, format: { with: EIN_RE }, allow_nil: true
  validates :ssn, format: { with: SSN_RE }, allow_nil: true

  def tin
    ssn || ein
  end

  class << self
    def find_by_tin(tin)
      where( ssn: tin ).or( where(ein: tin) )
    end
  end
end

I would also suggest that you store the data "normalized", without the dashes, just the numbers. This is simpler to work with, and the accepted format can be changed without having to change all the data. Format them in a decorator.