Build complex rails SQL

Question

Im using postgreSQL. Need scope for getting recent jokes with punchlines_count. I already achieve this with my scope. What i need know is my punchline counter should not include punchlines that has warn_level > 1. Warn model has punchline_id and weight, weight == warn_level. Please help me build this query. Clarifications: Punchline warn could be with weight 1 or 2, or could have 2 warns with weight 1 each. If warn_level > 1 i should not count it in my scope. Thanks!

My models.

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

Schema:

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

I think you're better off approaching this by creating a warn_level field on punchlines.

Much like the counter that ActiveRecord magically gives you, we can do something similar.

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 

When a warning is added you could manually call this method, or have an observer do it for you.

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

With this in place your problem becomes much simpler, we can do what you want with the following sql.

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

This can be expressed in 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

This looks pretty messy, but I think you've set yourself a difficult problem.

Hope this works for you.

Note: you could also cache the post_count as a column following a similar approach. But lets deal with this iteratively.

Answer 2

Your query could look like this. I filled in where your question was not clear.

SELECT j.id, j.joke, j.created_at, sum(w.weight) AS warn_level
FROM   jokes j
LEFT   JOIN punchlines p ON p.joke_id = j.id
LEFT   JOIN warns w ON w.punchline_id = p.id
GROUP  BY 1, 2, 3
HAVING COALESCE(sum(w.weight), 0) <= 1
ORDER  BY j.created_at DESC
LIMIT  my_limit;

Edit after more info

Adapted table names and primary key names (Automatically generated primary keys in ActiveRecord are named id). The rest was a direct hit. :)

I use LEFT JOIN now to safeguard against cases where jokes have no punchlines or punchlines have no warns at all. Hence the HAVING clause has to catch that case with COALESCE(), or jokes would be excluded just for having no punchline.
_{On the other hand, maybe they should? No punchline? What a joke?!}

Concerning GROUP BY

Up until PostgreSQL 9.0 you need to list all ungrouped columns of your SELECT in the GROUP BY clause. Starting with PostgreSQL 9.1 you can simply write GROUP BY j.id to cover all columns of the table jokes (id being the primary key).
I wrote more about that topic here: Is it possible to have an SQL query that uses AGG functions in this way?