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))
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.