On 8/28/06, Alex Moore
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:>
>
> Hey,
>
> I''m doing some stats on how many music videos where recorded in
the
> previous week by using the following sql query:
>
> select DATE_FORMAT(creation_date - interval 1 day,
''%Y-%m-%d'') as date,
> count(artist) as total
> from music_videos
> group by DATE_FORMAT(creation_date - interval 1 day,
''%Y-%u'')
> order by date desc
>
> it basically produces a date and a number
> 2005-06-01 39
>
> I''d like to use this data in rails to create a table and a graph,
but i
> have no idea how to get the data in since this data doesn''t match
any of
> my models.
>
> so my question is do i
> a)Create a model and somehow map the queries across to create the
> instances
> b)Pull in the sql(somehow) directly and parse the results line by line
> c)something I haven''t thought of?
>
> This is the first time i''ve come across this, so i''d like
to find out
> best practice etc
How about a MusicVideo.count_by_date method?
def self.count_by_date
# select [date, count] rows
dates_and_counts = connection.select_all(COUNT_BY_DATE_SQL)
# transform to { date => count, ... } hash
dates_and_counts.group_by { |row| row.first }
end
You can probably do it with calculations API - worth taking a look - but
hacking out the SQL this way is fine too.
jeremy
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---