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.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
Ilan Berci
2008-Feb-26 04:28 UTC
Re: Using group in with find in an ActiveRecord appropriatel
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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Feb 25, 9:28 pm, Ilan Berci <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Ilan Berci
2008-Mar-01 21:16 UTC
Re: Using group in with find in an ActiveRecord appropriatel
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, > RobAnother 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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Mar 1, 2:16 pm, Ilan Berci <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---