Rails 3: Merit Leaderboard (HMBTM association) for venues & seasons

Question

I am trying to understand how to pass my @venues variable into the 'merit_scores_override.rb' and use SQL to categorize based on the venues associated with that season. So when leaderboard has params of 'since_date' it takes all the venues from that season and adds those points up.

Currently it adds up points of all venues even when the season has only 2 of the venues associated with.

http://i62.tinypic.com/fjoxtc.png

http://i62.tinypic.com/mmstn9.png

First image should be 0 because those venues have no points associated with them.

StaticPages#leaderboard

def leaderboard
@seasons = Season.all
@season = Season.find_all_by_start(params[:since_date])

if(params.has_key?(:since_date))
  @venues = Season.find_by_start(params[:since_date]).venues
else
  @venues = Venue.all
end

    since_date = params[:since_date]
    end_date = params[:end_date]
category = params[:category]

if since_date.blank? && end_date.blank? && category.blank?
    @scores = Merit::Score.top_scored
elsif since_date.blank? && end_date.blank?
  @scores = Merit::Score.top_scored(category: category)
elsif category.blank?
  @scores = Merit::Score.top_scored(since_date: since_date, end_date: end_date, venues: @venues)
else
    @scores = Merit::Score.top_scored(since_date: since_date, end_date: end_date, category: category, venues: @venues)
end

end

merit_scores_override.rb

def self.top_scored(options = {})
  options[:table_name] ||= :users
  options[:since_date] ||= 10.years.ago
  options[:end_date] ||=  1.month.from_now
  options[:category] ||=  nil
  options[:limit]      ||= 25
  #options[:venues] ||=  nil


  alias_id_column = "#{options[:table_name].to_s.singularize}_id"
  if options[:table_name] == :sashes
    sash_id_column = "#{options[:table_name]}.id"
  else
    sash_id_column = "#{options[:table_name]}.sash_id"
  end

  # MeritableModel - Sash -< Scores -< ScorePoints
  sql_query = "SELECT
                #{options[:table_name]}.id AS #{alias_id_column},
                SUM(num_points) as sum_points
              FROM #{options[:table_name]}
                LEFT JOIN merit_scores ON merit_scores.sash_id = #{sash_id_column}
                LEFT JOIN merit_score_points ON merit_score_points.score_id = merit_scores.id
              WHERE merit_score_points.created_at > '#{options[:since_date]}' AND merit_score_points.created_at < '#{options[:end_date]}' "

  if(options[:category] != nil)
      sql_query += "AND merit_scores.category = \"#{options[:category]}\" "
  #else
   # options[:venues].each do |venue|
   #   sql_query += "AND merit_scores.category = \"#{venue.name}\" "
   # end
  end

  sql_query += "GROUP BY #{options[:table_name]}.id, merit_scores.sash_id
                ORDER BY sum_points DESC
                LIMIT #{options[:limit]} "

  results = ActiveRecord::Base.connection.execute(sql_query)
  results.map do |h|
    h.keep_if { |k, v| (k == alias_id_column) || (k == 'sum_points') }
  end
  results
end

Still very new to Ruby on Rails, anything helps. Really appreciate your time and hope this was thoroughly explained. The code commented out wasn't work but gets you an idea of what I am trying to do.

i having troubles to understand what you are trying to do, where is your habtm association?(i suppose you mean that with hmbtm? couldn't find info) — Alexis, Jun 10 '14 at 01:06
Each season has many venues and venues has many seasons, and when adding up the leader board I am trying to filter the points so that it only adds up the points associated with that season. The images are trying to show you that it adds up all the points versus what I want is the points from the 2 venues within 'Summer 2014'. — arinh, Jun 10 '14 at 07:06

score 0 · Answer 1 · answered Jun 10 '14 at 18:54

I somehow manage to answer my own questions every time within the next couple days. I ended up using these if statements to grab venues only within that season or do the individual venue.

  if(options[:category] != nil)
    sql_query += "AND merit_scores.category = \"#{options[:category]}\" "
  elsif(options[:venues] != nil)
    sql_venues = Array.new
    sql_query += "AND ("
    options[:venues].each do |venue|
      sql_venues.push("merit_scores.category = \"#{venue.name}\"")
    end
    sql_query += sql_venues.join(" OR ")
    sql_query += ") \n"
  end

Rails 3: Merit Leaderboard (HMBTM association) for venues & seasons

1 Answers1