0

My simplified rails app looks like this

# chapter.rb
has_many :sections
has_many :videos, through: :sections

# section.rb
belongs_to :chapter
has_many :videos

# video.rb
belongs_to :section
has_many :votes

# vote.rb
belongs_to :video

What I want to do is find the top 5 currently popular videos for a given chapter, which would mean ordering the videos by the number of votes it has received within the last month (and limiting to 5 results, obviously).

I wanted to write a popular_videos method in my Chapter model, and I think this requires a find_by_sql query, right? But I don't know enough sql to write this query.

The votes table has a created_at column, and I'm using postgres for my database. Any help is greatly appreciated.

n_i_c_k
  • 1,504
  • 10
  • 18

1 Answers1

1

I don't know anything about rails, but guessing at the table structures from what's in the question, I would think SQL like the following would be one way to provide what you want:

SELECT video.id, video.name, count(*) as vote_count
  FROM video JOIN vote ON (video.id = vote.video_id)
  WHERE vote.date > (current_date - interval '1 month')
  GROUP BY video.id, video.name
  ORDER BY vote_count DESC
  LIMIT 5;

If you're running PostgreSQL 9.1 or later, you can probably omit video.name from the GROUP BY list.

kgrittn
  • 18,113
  • 3
  • 39
  • 47
  • Thanks for the answer, but I also need the query to omit votes that are older than one month from the vote count – n_i_c_k May 14 '12 at 19:29
  • Guessing (again) at the actual table structure, I added a line to limit to votes in the last month. – kgrittn May 14 '12 at 20:33
  • worked great! thank you. I have one more question. I'm using postgre 9.0, and I want to select all columns from the videos table instead of just the name and id, so I changed first line to `SELECT *, count(*) as vote_count`, but then it kept giving me errors about the GROUP BY line. Do I need to add every column from the video table to this line, or is there a better way to do it? – n_i_c_k May 15 '12 at 02:17
  • In 9.0 you need to add every column which is not based on an aggregate function. In 9.1 it is enough to include the primary key, since PostgreSQL now takes advantage of the fact that every other column is dependent on the primary key. – kgrittn May 15 '12 at 11:09