statistics from models created

Question

I'm trying to figure out an efficient way to provide data to a plotting script jqplot of the amounts of clicks on a certain model

I have the main model: Listing has_many clicks each click has a datestamp how can I get a count of clicks created for each day in a date range?

Answer 1

def number_of_clicks
    start_date = Date.strptime(params[:start_date],"%d/%m/%Y")
    end_date = Date.strptime(params[:end_date],"%d/%m/%Y") 
    options = {conditions = ["date_stamp > ? and date_stamp < ?", start_date, end_date}
    Listing.find(:all, options).size
 end

Answer 2

date_from = Date.parse '2010-06-01'
date_to = Date.parse '2010-07-01'
click_counts = (date_from..date_to).map{|date|
  [date, Click.count(:conditions => {:date_stamp => date})]
}

will return an array of [date, count] pairs for a given period. However, this will issue a database query for each day in the date range.

One common solution to solve such queries is to create a database table filled with all dates (say, for previous and next 10 years from now), and then perform a JOIN, similar to:

SELECT d.date, COUNT(c.date_stamp)
FROM dates d
LEFT JOIN clicks c ON d.date = c.date_stamp
GROUP BY d.date

which will return all the click counts in a single query.

Or, as a third solution, you can get click counts for existing dates:

Click.find(:all,
  :select => "date_stamp, count(*) as count",
  :group => 'date_stamp',
  :conditions => ["date_stamp >= ? and date_stamp < ?", date_from, date_to])

and add "empty" days in Ruby.