Combining Active Record group, join, maximum & minimum

Question

I'm trying to get to grips with the Active Record query interface. I have two models:

class Movie < ActiveRecord::Base
  has_many :datapoints
  attr_accessible :genre
end

class Datapoint < ActiveRecord::Base  
  belongs_to :movie  
  attr_accessible :cumulative_downloads, :timestamp
end

I want to find the incremental downloads per genre for a given time period.

So far I've managed to get the maximum and minimum downloads per movie within a time period, like so:

maximums = Datapoint.joins(:movie)
    .where(["datapoints.timestamp > ?", Date.today - @timespan])
    .group('datatpoints.movie_id')
    .maximum(:cumulative_downloads)

This then allows me to calculate the incremental per movie, before aggregating this into the incremental per genre.

Clearly this is a bit ham-fisted, and I'm sure it would be possible to do this in one step (and using hash conditions). I just can't get my head around how. Can you help?

Much appreciated!

Derek.

Answer 1

I think this will allow you to calculate maximum per genre:

Movie.joins(:datapoints).where(datapoints: {timestamp: (Time.now)..(Time.now+1.year)}).group(:genre).maximum(:cumulative_downloads)

Edit 1

You can get the diffs in a couple of steps:

rel = Movie.joins(:datapoints).where(datapoints: {timestamp: (Time.now)..(Time.now+1.year)}).group(:genre)
mins = rel.minimum(:cumulative_downloads)
maxs = rel.maximum(:cumulative_downloads)
res = {}
maxs.each{|k,v| res[k] = v-mins[k]}

Edit 2

Your initial direction was almost there. All you have to do is calculate the diff per movie in the SQL and stage the data so you can collect it with one pass. I'm sure there's a way to do it all in SQL, but I'm not sure it will be as simple.

# get the genre and diff per movie
result = Movie.select('movies.genre, MAX(datapoints.cumulative_downloads)-MIN(datapoints.cumulative_downloads) as diff').joins(:datapoints).group(:movie_id) 
# sum the diffs per genre
per_genre = Hash.new(0)
result.each{|m| per_genre[m.genre] += m.diff}

Edit 3

Including the movie_id in the select and the genre in the group:

# get the genre and diff per movie
result = Movie
    .select('movies.movie_id, movies.genre, MAX(datapoints.cumulative_downloads)-MIN(datapoints.cumulative_downloads) as diff')
    .joins(:datapoints)
    .group('movies.movie_id, movies.genre') 
# sum the diffs per genre
per_genre = Hash.new(0)
result.each{|m| per_genre[m.genre] += m.diff}