0

I'm using merit gem to add a reputation system to my application. I need to find the amount of points a user added to a Post. The solution I came up with is summing the points the user added to a Post when she did a vote action: 

SELECT SUM(num_points)
FROM merit_actions
INNER JOIN merit_activity_logs
ON merit_activity_logs.action_id = merit_actions.id
INNER JOIN merit_score_points
ON merit_score_points.id = merit_activity_logs.related_change_id
INNER JOIN merit_scores
ON merit_scores.id = merit_score_points.score_id
WHERE target_id = post.id
AND target_model = post.class.table_name
AND user_id = post.id
AND sash_id = post.sash_id

These are the tables related to the reputation system:

create_table "merit_actions", force: :cascade do |t|
    t.integer  "user_id"
    t.string   "action_method"
    t.integer  "action_value"
    t.boolean  "had_errors",    default: false
    t.string   "target_model"
    t.integer  "target_id"
    t.text     "target_data"
    t.boolean  "processed",     default: false
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  create_table "merit_activity_logs", force: :cascade do |t|
    t.integer  "action_id"
    t.string   "related_change_type"
    t.integer  "related_change_id"
    t.string   "description"
    t.datetime "created_at"
  end

  create_table "merit_score_points", force: :cascade do |t|
    t.integer  "score_id"
    t.integer  "num_points", default: 0
    t.string   "log"
    t.datetime "created_at"
  end

  create_table "merit_scores", force: :cascade do |t|
    t.integer "sash_id"
    t.string  "category", default: "default"
  end

The related models copied from merit library:

class Action < ActiveRecord::Base
    self.table_name = :merit_actions

    has_many :activity_logs, class_name: 'Merit::ActivityLog'
end

class ActivityLog < ActiveRecord::Base
    self.table_name = :merit_activity_logs

    belongs_to :action, class_name: 'Merit::Action'
    belongs_to :related_change, polymorphic: true
    has_one :sash, through: :related_change
end

class Sash < ActiveRecord::Base
    include Base::Sash
    has_many :scores, dependent: :destroy, class_name: 'Merit::Score'
end

class Score < ActiveRecord::Base
    self.table_name = :merit_scores
    belongs_to :sash
    has_many :score_points,
             dependent: :destroy,
             class_name: 'Merit::Score::Point'

    def points
      score_points.group(:score_id).sum(:num_points).values.first || 0
    end

    class Point < ActiveRecord::Base
      belongs_to :score, class_name: 'Merit::Score'
      has_one :sash, through: :score
      has_many :activity_logs,
               class_name: 'Merit::ActivityLog',
               as: :related_change
      delegate :sash_id, to: :score
    end
  end

How Can I write the above SQL query for this purpose?

Sajad Rastegar
  • 3,014
  • 3
  • 25
  • 36
  • This seems more complicated than necessary because naming conventions are not put to good use. Not sure if your model definitions are complete. E.g. the `target` association seems to be missing on `Action`. Your SQL seems to be mixed with Ruby? – Raffael May 27 '16 at 00:00
  • You can chain joins like so: `Action.joins(:activity_logs => {:score_points => :scores})` (See the official Rails guide for the Query API). – Raffael May 27 '16 at 00:00
  • 1
    Maybe start off with an easier query to verify that your models are set up correctly. Do a few of those simple queries. Analyze work in progress using `to_sql` on the query. Study the API documentation of `belongs_to` etc. closely. Use conventional names wherever possible (e.g. `target_type` instead of `target_model`), or else the Rails magic will not work. Also, do not nest classes like that. Use Ruby modules for namespacing. – Raffael May 27 '16 at 00:07

0 Answers0