Rails 3 Join Question for Votes Table
- by Dex
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.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
