Here’s a class function I’ve made in a model called Exchange:
def self.get_history(product_id)
exchanges = Exchange.select(“product_id, quantity, week,
SUM(quantity) AS total”)
exchanges = exchanges.where(‘product_id = ?’, product_id)
exchanges = exchanges.group(“week”)
end
This gets me a total number of a particular widget sold by week. It
worked fine locally on Sqlite3 until I pushed it up to Heroku, which
uses PostgreSQL.
PostgreSQL complained that I needed to include everything in the
SELECT clause in the GROUP_BY. I did a search and sure enough, that’s
the case. It’s weird, everyone thinks so, but it’s the way life is.
It’s also weird that the ActiveRecord PostgreSQL adapter wouldn’t do
this for me. Anyway, I changed the group and it stopped complaining. I
stopped complaining too.
exchanges = exchanges.group(“week, product_id, quantity”)
I was back on the road… except… Now “total” is coming back as a
string, not an integer.
Console output from PostgreSQL:
Exchange.get_history(83).last.total
=> “20.25”
Console output from Sqlite3:
Exchange.get_history(83).last.total
=> 20.25
What the hey! Does that make seem busted to anyone?
I couldn’t find it documented anywhere so I found a solution purely by
trial and error. I modified the select:
exchanges = Exchange.select(“product_id, week, SUM(quantity) AS
quantity”)
So the “fix” was changing the AS portion of the SELECT clause. The
calculated column needs have the same name as the column being
operated on. Notice I also removed quantity from the list of selected
columns to avoid a clash.
[Rails 3.0.0.rc2 + Ruby 1.8.7]