Using group in with find in an ActiveRecord appropriately

rubyonrails-talk
1

I'm playing around with the Practical Rails Projects code, and was attempting to make a change for graphing data differently using groupings.

Basically the code went from:         total_weight = @exercise.activities.collect {|e| e.repetitions * e.resistance}         workout_dates = @exercise.activities.collect {|e| e.workout.date.to_s}

To:         total_weight = @exercise.activities.find(                                                  :all,                                                  :select => "sum( repetitions * resistance ) AS resistance",                                                  :group => "workout_id",                                                  :order => "workout_id"         ).collect{ |e| e.resistance }         workout_dates = @exercise.activities.find(                                                   :all,                                                   :group => "workout_id",                                                   :order => "workout_id"         ).collect {|e| e.workout.date.to_s}

Now, the total_weight array works fine in the new implementation, but the generation of it is much less than optimal. I'm renaming the sum as an existing column so I can collect it, which is just a nasty hack.

How can this be done better? I understand I can use Enumerable#group_by, but I want the db to do this one.

2

Rob wrote:

How can this be done better? I understand I can use Enumerable#group_by, but I want the db to do this one.

Rob,

If you want the db to do this one then just do the optimal SQL query yourself and load it up, for one, it will probably be easier to read than the complex meta you currently have.

I am never afraid to get my hands dirty with SQL as I am already committed to a particular vendor and sometimes I can fine tune the query much better than rails.

hth

ilan

3

Thanks. I now have:

        total_weight = Exercise.find_by_sql( ["           SELECT sum( repetitions * resistance ) AS total_weight           FROM exercises             JOIN workouts ON( activities.workout_id = workouts.id )             JOIN activities ON( activities.exercise_id = exercises.id )           WHERE exercises.id = ?             AND exercises.user_id = ?           GROUP BY workout_id           ORDER BY workout_id         ", @exercise.id, @exercise.user_id] ).collect{ |e| e.total_weight }

Cheers, Rob

4

Nah, I'm a SQLite and PostgreSQL guy... Which meant my sql was bad anyway. So now it's:

          SELECT sum( repetitions * resistance ) AS total_weight,                  MIN( date ) AS date           FROM activities             JOIN workouts ON( activities.workout_id = workouts.id )             JOIN exercises ON( activities.exercise_id = exercises.id )           WHERE exercises.id = ?             AND exercises.user_id = ?           GROUP BY workout_id           ORDER BY workout_id

Cheers, Rob