sql - 构建复杂的 Rails SQL

Question

我正在使用 postgreSQL。需要使用 punchlines_count 获取最近的笑话的范围。我已经用我的范围实现了这一点。我需要知道的是，我的妙语计数器不应包含 warn_level > 1 的妙语。警告模型具有 punchline_id 和权重，权重 == warn_level。请帮我建立这个查询。说明:妙语警告可以是权重 1 或 2，或者可以有 2 个警告，每个警告权重为 1。如果 warn_level > 1 我不应该把它算在我的范围内。谢谢!

我的模型。

class Joke < ActiveRecord::Base
  COLUMNS = self.column_names.map{|c| "jokes.#{c}" }.join(', ') 
  has_many :punchlines, :dependent => :destroy

  scope :recent, :order => 'jokes.created_at DESC'
  scope :recent_jokes_with_punchline_counter, lambda { |limit|
                        select("#{Joke::COLUMNS}, COUNT(punchlines.id) as punchlines_count").
                                             joins(:punchlines).
                                             group(Joke::COLUMNS).limit(limit) }


end

class Punchline < ActiveRecord::Base
  belongs_to :joke
  belongs_to :user
  has_many :warns
end


class Warn < ActiveRecord::Base
  belongs_to :punchline
  belongs_to :user
end

架构:

create_table "jokes", :force => true do |t|
    t.text     "content"
    t.integer  "user_id"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.integer  "up_votes",    :default => 0,     :null => false
    t.integer  "down_votes",  :default => 0,     :null => false
    t.string   "cached_slug"
    t.integer  "popularity"
    t.boolean  "anonymous",   :default => false
    t.string   "shorten_url"
  end

  create_table "punchlines", :force => true do |t|
    t.text     "content"
    t.integer  "user_id"
    t.integer  "joke_id"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.integer  "up_votes",    :default => 0,     :null => false
    t.integer  "down_votes",  :default => 0,     :null => false
    t.string   "cached_slug"
    t.boolean  "anonymous",   :default => false
  end

  create_table "warns", :force => true do |t|
    t.integer "punchline_id"
    t.integer "user_id"
    t.integer "weight"
  end
end

Answer 1

我认为您最好通过在 punchlines 上创建一个 warn_level 字段来解决这个问题。

就像 ActiveRecord 神奇地给你的 counter 一样，我们可以做类似的事情。

add_column :punchlines, :warn_level, :integer, :default => 0

class Punchline < ActiveRecord::Base
  def update_warn_level!
    self.update_attribute(:warn_level, self.warns.sum(:weight))
  end
end 

添加警告时，您可以手动调用此方法，或让观察者为您调用。

http://guides.rubyonrails.org/active_record_validations_callbacks.html#observers

class WarnObserver < ActiveRecord::Observer
  def after_create(model)
    if model.punchline
      model.punchline.update_warn_level!
    end
  end
end

# in your application.rb
config.active_record.observers = :warn_observer

有了这个，你的问题就变得简单多了，我们可以用下面的 sql 做你想做的事。

SELECT jobs.*, (
  SELECT COUNT(*) FROM punchlines
  WHERE punchlines.job_id = jobs.id
  AND punchlines.warn_level <= 1
) AS punchline_count

这可以用ActiveRecord来表达

PUNCHLINE_COUNT = <<-SQL
  SELECT COUNT(*) FROM punchlines
  WHERE punchlines.job_id = jobs.id
  AND punchlines.warn_level <= 1
SQL

def with_punchline_count
  select("jobs.*, (#{PUNCHLINE_COUNT}) AS punchline_count")
end

这看起来很乱，但我认为你给自己设置了一个难题。

希望这对你有用。

注意:您也可以按照类似的方法将 post_count 缓存为一列。但是让我们迭代地处理这个问题。