Forum: Ruby on Rails Help with Code Tweaking

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Lin W. (Guest)
on 2009-04-03 14:01
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
Philip H. (Guest)
on 2009-04-03 21:03
(Received via mailing list)
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.
This topic is locked and can not be replied to.