require 'memoist'

class County < ActiveRecord::Base
  extend Memoist
  belongs_to :state
  has_many :zipcodes

  validates :name, uniqueness: { scope: :state_id, case_sensitive: false },
                   presence: true

  scope :without_zipcodes, -> { joins("LEFT JOIN zipcodes ON zipcodes.county_id = counties.id").where("zipcodes.county_id IS NULL") }
  scope :without_state, -> { where("state_id IS NULL") }

  def cities
    zipcodes.map(&:city).sort.uniq
  end
  memoize :cities
end

require 'memoist'

class County < ActiveRecord::Base
  extend Memoist
  belongs_to :state
  has_many :zipcodes

  validates :name, uniqueness: { scope: :state_id, case_sensitive: false },
                   presence: true

  scope :without_zipcodes, -> {
    county = County.arel_table
    zipcodes = Zipcode.arel_table
    zipjoin = county.join(zipcodes, Arel::Nodes::OuterJoin)
        .on(zipcodes[:county_id].eq(county[:id]))
    joins(zipjoin.join_sources).where(zipcodes[:county_id].eq(nil))
  }
  scope :without_state, -> {
    where(state_id: nil)
  }

  def cities
    zipcodes.map(&:city).sort.uniq
  end
  memoize :cities
end

Convert raw SQL to Arel
Convert raw SQL to Arel
