Rails 3: Filtering merit points by category in leaderboard

Question

The real tactical question I am facing is all categories are set as 'default' therefore if I make options[:category] = 'default' it only adds the points that have no category. Therefore if i add points to cateogry 'arin' it will not be counted to the 'default' total. So I tried to grab all tables if NOT NULL or by category but it keeps grabbing the same amount for 'arin'.

default: 20

arin: 20

Should be total of 40 if category not supplied or at 'default', if params category 'arin' then it should be 20.

Can someone help me understand the concept behind the correct SQL to get the results I am looking for?

New to rails and SQL.

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

  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 = <<SQL
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]}' AND (merit_scores.category IS NOT NULL OR merit_scores.category = '#{options[:category]}')
GROUP BY #{options[:table_name]}.id, merit_scores.sash_id
ORDER BY sum_points DESC
LIMIT #{options[:limit]}
SQL

  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
  end

Answer 1

Seems no one answered and only down voted. Here is to anyone that questions this in the future. I figured out you can split sql statements and use an if statement in rails around the SQL.

  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]}\" "
  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)