Rails 3 Join Question for Votes Table

Question

I have a table posts and a polymorphic table votes.

The votes table looks like this:

create_table :votes do |t|
  t.references :user       # user_id
  t.integer    :vote       # the vote value
  t.references :votable    # votable_type and votable_id
end

I want to list all posts that the user has not yet voted on. Right now I'm basically taking all the posts they've already voted on and subtracting that from the entire set of posts. It works but it's not very convenient as I currently have it.

def self.where_not_voted_on_by(user)
  sql = "SELECT P.* FROM posts P LEFT OUTER JOIN ("
  sql << where_voted_on_by(user).to_sql
  sql << ") ALREADY_VOTED_FOR ON P.id = ALREADY_VOTED_FOR.id WHERE (user_id is null)"
  puts sql
  resultset = connection.select_all(sql)
  results = []
  resultset.each do |r|
    results << Post.new(r)
  end
  results
end

def self.where_voted_on_by(user)
  joins(:votes.outer).where("user_id = #{user.id}").select("posts.*, votes.user_id")
end

Answer 1

Okay, took me long enough, but the answer was much simpler:

joins(:votes.outer, "AND user_id = #{user.id}").where("votes.vote IS NULL")