gistfile1.rb

class UpdateDealAggregateRatings < ActiveRecord::Migration
  def up
    execute("
        update deals set aggregate_rating = (
          select avg(deal_rating_avg.by_user) avg_rating
          from (
            select avg(deal_ratings.rating) by_user, dr.user_id
            from deal_ratings
            join deal_reviews dr
            on dr.id = deal_ratings.deal_review_id
            and dr.deal_id = deals.id
            group by dr.user_id
          ) deal_rating_avg
        ); 
            ")
  end
end

gistfile2.rb

require 'integrations/integrations_spec_helper'
require "#{Rails.root}/db/migrate/20140213171901_update_deal_aggregate_ratings.rb"

describe UpdateDealAggregateRatings do

  def review_for(user, deal)
    DealReview.where(user_id:user.id, deal_id:deal.id).first ||
    create(:deal_review, deal: deal, user: user)
  end

  def rating_for(deal, user, rating, title)
    create(:deal_rating, rating: rating, deal_review: review_for(user, deal), rating_category: create(:rating_category, title: title))
  end

  let!(:user1) { create(:user)}
  let!(:user2) { create(:user)}

  let!(:deal_with_high_ratings) { create(:deal)}
  let!(:deal_with_low_ratings) { create(:deal) }
  let!(:deal_with_no_ratings) { create(:deal)}

  before do
    rating_for(deal_with_high_ratings, user1, 5, 'terms')
    rating_for(deal_with_high_ratings, user1, 5, 'market')
    rating_for(deal_with_high_ratings, user1, 5, 'product_service')
    rating_for(deal_with_high_ratings, user1, 4, 'management_team')

    rating_for(deal_with_high_ratings, user2, 4, 'terms')


    rating_for(deal_with_low_ratings, user1, 1, 'terms')
    rating_for(deal_with_low_ratings, user1, 1, 'market')
    rating_for(deal_with_low_ratings, user1, 2, 'product_service')

    rating_for(deal_with_low_ratings, user2, 2, 'terms')
    rating_for(deal_with_low_ratings, user2, 2, 'market')
    rating_for(deal_with_low_ratings, user2, 3, 'product_service')
  end


  it "updates each deal's aggregate_rating to the average of all the users's average ratings for that deal" do
    UpdateDealAggregateRatings.new.up

    deal_with_high_ratings.reload.aggregate_rating.should == 4.375

    deal_with_low_ratings.reload.aggregate_rating.should == 1.83333333333333

    deal_with_no_ratings.reload.aggregate_rating.should be_nil
  end

end