Return hash with multiple key/value pairs from raw sql query

Question

I'm building a ruby on rails application that uses raw SQL to query my database.

Lets say for simplicity’s sake, I have the following records in Table1:

<date:"2020-06-09 23:10:00", pid: 1, price: 5, tax: 0.65, shipping: 10>
<date:"2020-06-09 13:00:00", pid: 1, price: 10, tax: 1.3, shipping: 12>
<date:"2020-06-10 10:00:00", pid: 2, price: 20, tax: 2.6, shipping: 11>
<date:"2020-06-10 11:00:00", pid: 3, price: 5, tax: 0.65, shipping: 13>

My query is supposed to return a hash where each key represents a day, and the values would be the count of unique pid and a summation of the rest of the values. So the response in this case would be:

{
   2020-06-09: {
      pid_count: 1,
      price: 15,
      tax: 1.95,
      shipping: 22
   },
   2020-06-10: {
      pid_count: 2,
      price: 25,
      tax: 3.25,
      shipping: 24
   }
}

My query is:

query = <<-SQL
  SELECT TO_CHAR(TO_TIMESTAMP(table1.date), 'YYYY-MM-DD'), COUNT(DISTINCT table1.pid) as pid_count,
  sum(table1.price) as price, sum(table1.tax) as tax, sum(table1.shipping) as shipping from table1
  group by TO_CHAR(TO_TIMESTAMP(table1.date), 'YYYY-MM-DD')
SQL
results = ActiveRecord::Base.connection.execute(query).values

The calculation is correct but the problem is this doesn't group them properly into a hash. Calling .to_h on results gives an error ArgumentError (wrong array length at 0 (expected 2, was 5))

Answer 1

The reason you can't call to_h is because the method is expecting the structure:

[
  [key, value],
  [key, value],
  # ...
]

whereas you have the structure:

[
  [value_1, value_2, value_3, value_4, value_5],
  [value_1, value_2, value_3, value_4, value_5],
  # ...
]

To correctly convert it into the structure you want you have to tell to_h how to do so by providing a block.

column_names = %i[pid_count price tax shipping]
hash = results.to_h { |date, *values| [date, column_names.zip(values).to_h] }

This tells the to_h call to use the date as key, then zip the column names with the other values and convert them to a hash and use them as value.

The above might solve the issue, but in most cases there is no need to use execute to start with. Assuming table1 has a model I'd recommend using model methods instead like group and select/pluck. Those can be given SQL or Arel for more complex queries.