Converting rails 3 mySQL query to work with PostgreSQL

Question

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.

araqnid · Accepted Answer · 2012-01-12 02:04:33Z

1

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.

answered Jan 12, 2012 at 2:04

araqnid

135k25 gold badges163 silver badges138 bronze badges

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

1 Comment

awvidmer Over a year ago

Actually, the order clause comes from my initial thought to try and load all a user's transactions at once, in sorted date order, and then perform other operations on them for various purposes: including viewing them as selected, then regrouping and summing them for another view, thereby hitting the db only once. (In fact, the first part, up to the order clause, was originally in a variable.) While I now recognize that the order clause is irrelevant in this context, I suppose I thought that it would be ignored if benignly unnecessary, and in fact mySQL does ignore it. Thanks for the response!

Collectives™ on Stack Overflow

Converting rails 3 mySQL query to work with PostgreSQL

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