CODEWITHSUNDEEP
mysqlsqlruby-on-rails-3postgresql
awvidmer
awvidmer

Reputation: 155

Converting rails 3 mySQL query to work with PostgreSQL

I understand that the "group_by" selector is understood differently by mySQL and PostgreSQL, thanks to questions posted here. However, try as I might, I can't get the following (seemingly) simple query to work with the Heroku (PostgreSQL) db, while it works fine in my development mySQL environment:

@trans_by_trip = @user.transactions.order("date").sum(:amount_cents, :group => :trip_id)

I read that by including a calculation like "sum," group_by would work, but it doesn't. And I must admit that I'm at a loss to translate the query into raw SQL.

Many thanks for any help.

Upvotes: 0

Views: 205

Answers (1)

araqnid
araqnid

Reputation: 133402

transactions.order("date") - is this trying to order the transactions by date? if so, that's not really compatible with summing them grouped by something that doesn't include date. So try simply taking that clause out?

you can ask for the results to be ordered by (in SQL terms) min(date) which would return a result for each trip, ordered by the first date for that trip. Or max(date) etc. Not specifying min or max leaves the result ambiguous, which is why PostgreSQL rejects it.

Upvotes: 1

Related Questions