Cross-posted to Stack Overflow.
http://stackoverflow.com/questions/4551465/is-it-possible-to-construct-dynamic-aggregate-columns-in-an-arel-query-that-uses
Here's a bit of sample context for my question below to help clarify
what I'm asking...
The Schema
Users
- id
- name
Answers
- id
- user_id
- topic_id
- was_correct
Topics
- id
- name
The Data
Users
id | name
1 | Gabe
2 | John
Topics
id | name
1 | Math
2 | English
Answers
id | user_id | topic_id | was_correct
1 | 1 | 1 | 0
1 | 1 | 1 | 1
1 | 1 | 2 | 1
1 | 2 | 1 | 0
1 | 2 | 2 | 0
What I'd like to have, in a result set, is a table with one row per
user, and two columns per topic, one that shows the sum of correct
answers for the topic, and one that shows the sum of the incorrect
answers for that topic. For the sample data above, this result set
would look like:
My desired result
users.id | users.name | topic_1_correct_sum | topic_1_incorrect_sum |
topic_2_correct_sum | topic_2_incorrect_sum
1 | Gabe | 1 | 1 |
1 | 0
2 | John | 0 | 1 |
0 | 1
Obviously, if there were more topics in the Topics table, I'd like
this query to include new correct_sum and incorrect_sums for each
topic that exists, so I'm looking for a way to write this without
hard-coding topic_ids into the sum functions of my select clause.
Is there a smart way to magic this sort of thing with ARel?
--
You received this message because you are subscribed to the Google Groups "Ruby
or Rails Oceania" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rails-oceania?hl=en.
