Help with Code Tweaking

Rails
1

scenario : select distinct years from members and their respective
record count of that particular year.

eg: [{year = 2008 , count = 45123},{year = 2007 , count = 12332} ]

previously , i was using “select distinct tochar(datetime)” , it ended
up taking quite a while to fetch the records so i switched to this code.

relationship group 1 : N members

rootgroup is a pretched group

member = root_group.members.find(:all,
:select => “max(datetime) as maxdate,min(datetime) as mindate”
)[0]

return member.mindate.year.upto(member.maxdate.year).inject({}) do
|year|

          #return hash with year and member count for that year

          compare_date = Date.new(year,1,1)

          {year , root_group.members.find(
           :conditions => [ "datetime >= ? AND datetime < ?",
                             compare_date,
                             compare_date + 1.year ],
                            :select => 'count(*)')}
       end

any ideas on how to tweak it ?

it really looks quite clumsy at the moment
and iam repeating the code for drilling into the months and days ,
it really feels like bad practice.

thanks

2

Why not use something like this:

=# select count(*), date_part(‘minute’, created_at) as min from toys
group by min;
count | min
-------±----
4008 | 36
5239 | 37
4496 | 38
(3 rows)

Replacing “date_part” with whatever date function your database
supports to return the year. In my example I used minutes only
because all the data was created at the same time.

Worth benchmarking at least.