Mapping an array from SQL to Ruby

Question

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?

C-RAD · Accepted Answer · 2021-08-20 16:38:33Z

2

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

answered Aug 20, 2021 at 16:38

C-RAD

1,15712 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mu is too short Over a year ago

Better would be to convert the c = X1 or c = X2 or c = X3 or ... expression to c in (X1, X2, X3, ...); less repetitive, easier to read, and easier to convert to ActiveRecord style where calls later.

Collectives™ on Stack Overflow

Mapping an array from SQL to Ruby

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related