Using group in with find in an ActiveRecord appropriately

Rails
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

On Feb 25, 9:28 pm, Ilan B. [email protected]
wrote:

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.

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

On Mar 1, 2:16 pm, Ilan B. [email protected]
wrote:

Another little trick if you are using MySQL is to use the undocumented
method all_hashes in the mysql adapter which converts the unwieldy
result set into a regular hash which is a lot more convenient to work
with…

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

5

Rob wrote:

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

Another little trick if you are using MySQL is to use the undocumented
method all_hashes in the mysql adapter which converts the unwieldy
result set into a regular hash which is a lot more convenient to work
with…

ilan