CODEWITHSUNDEEP
sqlruby
cbln
cbln

Reputation: 157

Mapping an array from SQL to Ruby

I'm trying to turn some SQL into a Ruby Script, and I have written the following code

def creditor_numbers
      %w[
      and (
      or companies.data->>'creditor_number' = '900000424'
      or companies.data->>'creditor_number' = '900000425'
      or companies.data->>'creditor_number' = '900000511'
      or companies.data->>'creditor_number' = '900000579'
      or companies.data->>'creditor_number' = '900000567'
      or companies.data->>'creditor_number' = '900000568'
      or companies.data->>'creditor_number' = '900000569'
      or companies.data->>'creditor_number' = '900000570'
      or companies.data->>'creditor_number' = '900000584'
      or companies.data->>'creditor_number' = '900000573'
      ].join("\n. ")
    end

as so

“(#{[424,425,511,579,567,568,569,570,584,573].map { |x| “companies.data->>'creditor_number' = '900000#{x} ” }.join(‘ or ’) }).

Is there a better way to go about doing this?

Upvotes: 0

Views: 101

Answers (1)

C-RAD
C-RAD

Reputation: 1029

I think you have the right idea. A lot of the time when I see people dynamically building custom SQL, they build an array of predicates and an array of bind params and add them to the query at the end.

You should also sanitize the parameters if they can be edited by the end user to prevent sql injection. If you are using Rails, the ? operator will do this.

CREDITOR_COMPARISON = “companies.data->>'creditor_number' = ?”
SUFFIXES = [424,425,511,579,567,568,569,570,584,573]

def creditors
  Creditor.find_by_sql(
    <<-SQL
      SELECT * FROM creditors
      WHERE <some condition>
      AND (
        #{creditor_comparisons}
      )
    SQL,
    *creditor_numbers
  )
end

def creditor_numbers
  SUFFIXES.map{ |suffix| "900000#{suffix}" }
end

def creditor_comparisons
  Array.new(SUFFIXES.length, CREDITOR_COMPARISON).join("\n or")
end

Upvotes: 2

Related Questions