Forum: Ruby on Rails group by + sum

Hi.. I need some support...

table:
week_id, user_id, project_id, hours
ex. =>
33, 2, 1, 10
34, 2,1,15
33, 2, 2, 20
35, 3, 1,20
etc.

Want to display a sum of hours per week_id per user_id
I have:

@hours = HourUser.includes(:user).group_by { |h| h.week_id }

@hours.keys.sort.each do |hour|
@hours[hour].collect(&:stunden).sum

Hours are summed up, but not sorted by user_id..
How to get that?

Thanks
Werner

On Thu, Jan 24, 2013 at 2:35 AM, Werner 
<webagentur.laude@googlemail.com> wrote:
>
> Want to display a sum of hours per week_id per user_id
> I have:
>
> @hours = HourUser.includes(:user).group_by { |h| h.week_id }
>
> @hours.keys.sort.each do |hour|
> @hours[hour].collect(&:stunden).sum
>
> Hours are summed up, but not sorted by user_id..
> How to get that?

Hash#sort_by

Feel a litte ittle stupid right now..
where to sort_by?


Am Donnerstag, 24. Januar 2013 09:49:33 UTC+1 schrieb Jordon Bedwell:

On Thu, Jan 24, 2013 at 3:14 AM, Werner 
<webagentur.laude@googlemail.com> wrote:
> Feel a litte ittle stupid right now..
> where to sort_by?

Well, without a coherent though (AKA proper data outputs in the form
of say a copy of the pry session where it outputs => { } and then you
go through the steps I cannot say.  I have no idea about your code and
your examples are pretty broad as to the data you are working with.

Think, you mean something like:

Model.sort_by{|h| [h.user_id, -h.week_id]}
?





Am Donnerstag, 24. Januar 2013 10:24:57 UTC+1 schrieb Jordon Bedwell:

On Thu, Jan 24, 2013 at 4:35 PM, Werner 
<webagentur.laude@googlemail.com>wrote:

> Hi.. I need some support...
>
> table:
> week_id, user_id, project_id, hours
> ex. =>
> 33, 2, 1, 10
> 34, 2,1,15
> 33, 2, 2, 20
> 35, 3, 1,20
> etc.


> Want to display a sum of hours per week_id per user_id
> I have:
>
> @hours = HourUser.includes(:user).group_by { |h| h.week_id }
>
> @hours.keys.sort.each do |hour|
> @hours[hour].collect(&:stunden).sum
>

Look at
http://api.rubyonrails.org/classes/ActiveRecord/Ca...

sums = HourUser.sum(:hours, group: [:week_id, :user_id], order: 
:user_id)

You'll end up with something like [33, 2] => 70, [34, 2] => 15, [35, 3] 
=>
20
Just remember that to get a certain value, you'll have to pass an array 
as
the index
ie sums[[33,2]] to get 70



>
>
>



--

Hi Jim.. thanks so far..

in the moment this is a bit too far for me.

Just remember that to get a certain value, you'll have to pass an array 
as
the index
ie sums[[33,2]] to get 70
=> this is unclear
Pls. be so kind to explain the view part.

Werner






Am Donnerstag, 24. Januar 2013 11:36:44 UTC+1 schrieb jim:

On Thu, Jan 24, 2013 at 7:55 PM, Werner 
<webagentur.laude@googlemail.com>wrote:

>
since the keys of the hash is an array, you need to use an array as the
index to get a value

>> sums = { [33, 2] => 70, [34, 2] => 15, [35, 3] => 20 }
>> sums[34,2]
ArgumentError: wrong number of arguments (2 for 1)
from (irb):3:in `[]'
from (irb):3
>> sums[[34,2]] # 15

the keys are defined by the group option you passed to #sum, so if you 
pass
as sql statement to
the group option, you'll get that as key. ie (postgre)

>> HourUser.sum(:hours, group: "week_id || ' --- ' || user_id", order:
:user_id)
>> { '33 --- 2' => 70, '34 --- 2' => 15, '35 --- 3' => 20 }

hope this helps



>>
>>> 33, 2, 2, 20
>>> @hours[hour].collect(&:**stunden).sum
>> as the index
>>>
>>>
>>
> To view this discussion on the web visit
> https://groups.google.com/d/msg/rubyonrails-talk/-....
>
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>



--

Jim..sorry.. not my day...
have to contemplate about your solution.

I wonder..

HourUser.includes(:user).where.....group_by { |h| h.week_id }

<% @hours.keys.sort.each do |h| %>
 <%= @hours[h].collect(&:hour).sum %></td>
<% end %>

is giving me what I want, just needs to be grouped by user_id

Thanks so far.



Am Donnerstag, 24. Januar 2013 14:16:16 UTC+1 schrieb jim:

On Thu, Jan 24, 2013 at 10:38 PM, Werner 
<webagentur.laude@googlemail.com>wrote:

>
> is giving me what I want, just needs to be grouped by user_id
>
> Thanks so far.
>

So given the current solution you have, you also want to group by 
user_id
right?
so here's how it should go.

@hour_users = HourUser.all.group_by(&:week_id)

gives you a hash with week_ids as keys

@hour_users.each do |week_id, by_week|
  by_week.group_by(&:user_id).each do |user_id, hour_users|
    hour_users.map(&:hour).sum
  end
end

using my first suggestion

@hour_users = HourUser.sum(:hour, group: [:week_id, :user_id])

@hour_users.each do |(week_id, user_id), total_hours|
  # do something with week_id, user_id and total_hours
end

Good luck!

>>> Hi Jim.. thanks so far..
>>
>> the keys are defined by the group option you passed to #sum, so if you
>>
>>>>
>>>>> 33, 2, 2, 20
>>>>> @hours[hour].collect(&:**stunden**).sum
>>>> Just remember that to get a certain value, you'll have to pass an array
>>>>> Werner
>>>>> googlegroups**.com.
>>>>
>>> googlegroups.com.
>>
> rubyonrails-talk+unsubscribe@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/rubyonrails-talk/-....
>
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>



--

Well.. I cant get it working

Try to explain it again..

db_table:
week_id, project_id, user_id, hour
ex. =>
33, 1, 1, 10
33, 4, 1, 20
33, 1, 2, 0
34, 1, 2, 15
34, 1, 1, 0


So, user with the id 1 worked 10 hours in week 33 and 20 in the week 33,
but other project
I want to show all hours summed up per week per user
row1(user1) => week 33 => 30, week 34 => 0
row2(user2) => week 33 => 0, week 34 => 15

Step one:
I want one row per user, so I group:
@hours = HourUser.all.group_by(&:user_id)

view:
@hours.each do |user, weeks|

gives me each user in one <tr>
within this row, each week one cell

<% weeks.group_by(&:week_id).each do |week, hours| %>
<td><%= hours.map(&:hour).sum %>

Shows the hours but not summed up. Instead I get :
row 1 =>  ...10.. and does not stop the row but starts again with ... 
20...
row 2 =>  ...0 15

How to? Do I also have to group otherwise..?
Thanks for support.


























Am Donnerstag, 24. Januar 2013 16:01:06 UTC+1 schrieb jim:

On Fri, Jan 25, 2013 at 9:42 PM, Werner 
<webagentur.laude@googlemail.com>wrote:

> 34, 1, 2, 15
> I want one row per user, so I group:
>
> Shows the hours but not summed up. Instead I get :
> row 1 =>  ...10.. and does not stop the row but starts again with ... 20...
> row 2 =>  ...0 15
>

so this is your remaining problem right?  I think the hour column is
a string which results to a concat of the values instead of simple
addition.  try hours.map { |h| h.hour.to_f }.sum


>
>
>>
>>> <% @hours.keys.sort.each do |h| %>
>> so here's how it should go.
>>
>>>
>>>>>
>>>> since the keys of the hash is an array, you need to use an array as the
>>>> pass as sql statement to
>>>>>
>>>>>>
>>>>>>> 33, 2, 2, 20
>>>>>>> @hours[hour].collect(&:**stunden****).sum
>>>>>> Just remember that to get a certain value, you'll have to pass an
>>>>>>> Werner
>>>>>>> googlegroups****.com.
>>>>>>
>>>>> To post to this group, send email to rubyonra...@googlegroups.**com.
>>>>>
>>> To post to this group, send email to rubyonra...@googlegroups.**com.
>>>
> To post to this group, send email to rubyonrails-talk@googlegroups.com.
> To unsubscribe from this group, send email to
> rubyonrails-talk+unsubscribe@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/rubyonrails-talk/-....
>
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>



--

Hi Jim..
thanks..
looks good now.. I had a mistake in another model..so the grouping was 
not
correct.

my god.. what a trip sometimes..

Werner

Am Freitag, 25. Januar 2013 14:50:53 UTC+1 schrieb jim: