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.
On Apr 3, 2009, at 3:01 AM, Lin Wj wrote:
>
> 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
> --
> 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
-~----------~----~----~----~------~----~------~--~---