# Using group in with find in an ActiveRecord appropriately

**URL:** <https://www.ruby-forum.com/t/using-group-in-with-find-in-an-activerecord-appropriately/186764>\
**Category:** Rails\
**Created:** [February 25, 2008, 9:36pm UTC](https://www.ruby-forum.com/t/using-group-in-with-find-in-an-activerecord-appropriately/186764 "2008-02-25T21:36:09Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![rob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/838e76/32.png) [@rob](https://www.ruby-forum.com/u/rob)\
**Post date:** [February 25, 2008, 9:36pm UTC](https://www.ruby-forum.com/t/using-group-in-with-find-in-an-activerecord-appropriately/186764/1 "2008-02-25T21:36:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![rob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/838e76/32.png) [@rob](https://www.ruby-forum.com/u/rob)\
**Post date:** [February 26, 2008, 5:28am UTC](https://www.ruby-forum.com/t/using-group-in-with-find-in-an-activerecord-appropriately/186764/2 "2008-02-26T05:28:40Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/838e76/32.png) [@rob](https://www.ruby-forum.com/u/rob)\
**Post date:** [March 1, 2008, 8:35pm UTC](https://www.ruby-forum.com/t/using-group-in-with-find-in-an-activerecord-appropriately/186764/3 "2008-03-01T20:35:58Z")

</div>

On Feb 25, 9:28 pm, Ilan B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
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

---

<div class="post-metadata">

**Author:** ![rob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/838e76/32.png) [@rob](https://www.ruby-forum.com/u/rob)\
**Post date:** [March 2, 2008, 1:45am UTC](https://www.ruby-forum.com/t/using-group-in-with-find-in-an-activerecord-appropriately/186764/4 "2008-03-02T01:45:42Z")

</div>

On Mar 1, 2:16 pm, Ilan B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
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

---

<div class="post-metadata">

**Author:** ![rob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/838e76/32.png) [@rob](https://www.ruby-forum.com/u/rob)\
**Post date:** [March 1, 2008, 10:16pm UTC](https://www.ruby-forum.com/t/using-group-in-with-find-in-an-activerecord-appropriately/186764/5 "2008-03-01T22:16:57Z")

</div>

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