ruby on rails group by with null values problem

Question

I have an hour table in witch I store user time tracking information, the table consists from the following cells

project_id
task_id (optional can be null)
worker_id
reported_date
working_hours

each worker enters several records per day so generally the table is looking like this

id project_id  worker_id task_id   reported_date  working hours;    
== =========== ========= ========= =============  ==============
1  1           1         1         10/10/2011      4                 
2  1           1         1         10/10/2011     14                 
3  1           1                   10/10/2011      4                 
4  1           1                   10/10/2011     14

the task_id is not a must field so there can be times when the user is not selecting it and their task_id cell is empty
now i need to display the data by using group by clause
so the result will be something like this:

project_id worker_id task_id   working hours
========== ========= ========= ==============
1          1         1         18                 
1          1                   18

I did the following group by condition:

@group_hours = Hour.group('project_id,worker_id,task_id)').
      select('project_id, task_id ,worker_id,sum(working_hours) as 
         working_hours_sum')

My view looks like this

<% @group_hours.each do |b| %>
     <tr>
       <td><%= b.project.name if b.project %></td>
        <td><%= b.worker.First_name if b.worker %></td>
        <td><%= b.task.name if b.task %></td>
        <td class="center"><%= b.working_hours_sum %></td>
       <td></td>
     </tr>
<% end %>

This it is working but only if the task_id is not null when task id is null it present all the records without grouping them like this

project_id  worker_id task_id   working hours
=========== ========= ========= ==============
1           1         1         18                 
1           1                    4            
1           1                   14

I will appreciate any kind of solution to this problem

lol, i know that. see how much cleaner i've made it? (deleted 3000+ characters from your original!) — Zabba
– Zabba, Commented Feb 28, 2011 at 23:30

macarthy · Accepted Answer · 2011-02-28 23:41:15Z

3

Assuming you are using mysql you can use the COALESCE function to work with nulls, by changing the null value to something else

e.g.

select COALESCE(colname,0) from table where COALESCE(colname,0) > 1;

IFNULL() is another option

answered Feb 28, 2011 at 23:41

macarthy

3,0692 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mu is too short Over a year ago

COALESCE is the standard SQL function for this sort of thing. AFAIK, IFNULL is a MySQL-ism and NVL the Oracle version but they all support COALESCE these days.

macarthy Over a year ago

Yeah I thought was everywhere COALESCE but didn't want to say for sure, but actually it does seem to be everywhere, even sqlite

Collectives™ on Stack Overflow

ruby on rails group by with null values problem

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related