blog@osd.se

I recently started working with Rails again. I haven’t gotten very far yet and I’m still re-learning all the stuff I forgot since last time. Today I wrote this piece of code:
user.transactions.sum('ABS(amount)', :joins =>
"INNER JOIN tags_transactions ON transactions.id = tags_transactions.transaction_id " +
"INNER JOIN tags ON tags_transactions.tag_id = tags.id", :group => "tags.name")
It works, but I don’t really like to have dependencies on my table names in my code (even if it’s in the model). I wonder if there’s a way to write this in a better way? (There probably is. The problem is to find out how.)

What I’m trying to do is to group by a field that I have to join in (in two steps). In this case it’s a tag name. (An account belongs to a user and has many transactions. A transaction has, and belongs to, many tags.) The full SQL of the (hand-written) query is:
SELECT sum(ABS(amount)), tags.name
FROM transactions
INNER JOIN accounts ON transactions.account_id = accounts.id
INNER JOIN tags_transactions tt ON transactions.id = tt.transaction_id
INNER JOIN tags ON tt.tag_id = tags.id
WHERE ((accounts.user_id = 1))
GROUP BY tags.name;


Update 2009-10-09: The answer was simple. You can write it like this
group = user.transactions.sum('ABS(amount)', :joins => :tags, :group => 'tags.name')and Rails will figure out how to join in ‘tags’ (which is specified in the models). Should have tried that first…