CODEWITHSUNDEEP
ruby-on-railsrubyactiverecordmodel
Antarr Byrd
Antarr Byrd

Reputation: 26061

Change scope to use sql

I have two ruby scope but they are not completely using activecord. How can I change it to use only activerecord or sql?

article.rb

class Article < Comfy::Cms::Page
  has_many :view_mappings
  has_many :favorite_mappings
  scope :top_by_number_of_favorites, -> { article.sort_by(&:favorite_score).take(10) }

  scope :top_by_number_of_views, -> { article.sort_by(&:view_score).take(10) }

  def favorite_score
    favorite_mappings.since(2.weeks.ago).count * 5
  end

  def view_score
    view_mappings.since(2.weeks.ago).count
  end

schema.rb

  create_table "view_mappings", force: :cascade do |t|
    t.integer  "article_id"
    t.date     "date_viewed"
    t.string   "device_id"
    t.datetime "created_at",  null: false
    t.datetime "updated_at",  null: false
  end

  create_table "favorite_mappings", force: :cascade do |t|
    t.integer  "article_id"
    t.string   "device_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.date     "date_liked"
  end

Upvotes: 2

Views: 80

Answers (2)

dhouty
dhouty

Reputation: 1989

Try this:

class Article < Comfy::Cms::Page
  has_many :favorite_mappings
  has_many :view_mappings

  def self.top_by_number_of_favorites(since = 2.weeks.ago, amount = 10)
    with_favorite_score
      .where('date_liked >= ?', since)
      .order('favorite_score DESC')
      .limit(amount)
  end

  def self.top_by_number_of_views(since = 2.weeks.ago, amount = 10)
    with_view_score
      .where('date_viewed >= ?', since)
      .order('view_score DESC')
      .limit(amount)
  end

  def self.with_favorite_score
    joins(:favorite_mappings)
      .select('articles.*, COUNT(favorite_mappings.id) AS favorite_score')
      .group(:id)
  end

  def self.with_view_score
    joins(:view_mappings)
      .select('articles.*, COUNT(view_mappings.id) AS view_score')
      .group(:id)
  end
end

I also made it so you can change the lookback date, so instead of 2 weeks, you could get the top articles from the last month, year, or since any arbitrary date. The limit is now changeable as well, so you can get the top 20, 50, 100, etc. You can also access the favorite_score/view_score like this @articles.top_by_number_of_views.first.view_score.

Upvotes: 2

EugZol
EugZol

Reputation: 6545

You should use some SQL magic:

  scope :top_by_number_of_favorites, -> {
    joins(:favorite_mappings).
    where("date_liked >= ?", 2.weeks.ago).
    group(:id).
    select("comfy_cms_pages.*, COUNT(*) AS favorite_score").
    order("favorite_score DESC").
    limit(10)
  }

First two lines – we join favorite_mappings to query result, selecting only ones which are 2 weeks old or newer. Then we group results by arcticle IDs, specifying that we want to select COUNT of likes. And then we just order them top to bottom, and taking first 10 records.

More on scopes:

http://guides.rubyonrails.org/active_record_querying.html#scopes

More on aggregate functions:

https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html http://www.postgresql.org/docs/9.1/static/functions-aggregate.html

Please note that you will have article['favorite_score'] for score. Also, all other fields of Article should be available to your code, as we specified * in list of fields to select.

Upvotes: 1

Related Questions