How to find unrated models with acts_as_rateable

Question

I have a rails application using the acts_as_rateable plugin.

I'm stuck on figuring out how to retrieve unrated models using this plugin - however this is more of a general rails/SQL question than specific to this plugin.

Acts as rateable adds the following to the schema:

create_table "ratings", :force => true do |t|
    t.integer  "rating",                      :default => 0
    t.string   "rateable_type", :limit => 15, :default => "", :null => false
    t.integer  "rateable_id",                 :default => 0,  :null => false
    t.integer  "user_id",                     :default => 0,  :null => false
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  add_index "ratings", ["user_id"], :name => "fk_ratings_user"

And my rated models also have a user id column.

I'd like to be able to retrieve all instances of a particular model that haven't been rated at all, and also all instances that haven't been rated by someone other than the creator of the model, e.g. here is one model:

class Review < ActiveRecord::Base
  acts_as_rateable

  belongs_to :user
  ...
end

So I want something like the following pseudocode

Review.find(:all, :conditions=>"not rated by anyone")
Review.find(:all, :conditions=>"not rated by anyone except review.user")

However I can't figure out the SQL to do this, nor the rails magic to generate that SQL :-)

Update: this query seems to find all models that ARE rated by somebody other than the user that owns the model. So I think I just need to invert this somehow.

 Review.find(:all, 
  :joins=>'left join ratings on reviews.id=ratings.rateable_id && ratings.rateable_type="Review"', 
  :conditions=>'reviews.user_id <> ratings.user_id', 
  :group=>'reviews.id')

Answer 1

Named scopes are the way to go for this problem. I would add two named scopes to your Review model. Something like:

class Review < ActiveRecord::Base
  acts_as_rateable

  belongs_to :user

  named_scope :not_rated, :conditions => { :rating => 0 }
  named_scope :not_rated_by_others,
              :conditions => ["user != ? AND rating == 0", self.user]
end

Then you can do:

@not_rated = Review.not_rated
@not_rated_by_others = Review.not_rated_by_others

There's a Railscast that explains named scopes.

---

EDIT: Second Attempt

Right, let's have another go! One of the problems is that there are multiple acts_as_rateable plugins out there. I've been testing using this one on RubyForge.

class Review < ActiveRecord::Base
  belongs_to :user

  acts_as_rateable

  named_scope :not_rated, :select => 'reviews.*',
              :joins => 'left join ratings on reviews.id = ratings.rateable_id',
              :conditions => 'ratings.rateable_id is null'

  named_scope :not_rated_by_others, lambda { |user| { :select => 'reviews.*',
              :joins => 'left join ratings on reviews.id = ratings.rateable_id',
              :conditions => ['ratings.user_id != ? and ratings.rateable_id is null',
                 user] }}
end

Use it like this:

frank = User.find_by_name('frank')
@not_rated = Review.not_rated
@not_rated_by_others = Review.not_rated_by_others(frank)

Answer 2

I've got some of the way using a query like this:

Review.find(:all, 
 :joins=>'left outer join ratings on reviews.id=ratings.rateable_id && ratings.rateable_type="Review"',
  :conditions=>'ratings.rating is NULL')

This looks like it returns all Review models with no rating at all.

And I think this one is working to find all Review models that aren't rated by the user who created the review:

Review.find(:all, 
  :joins=>'left outer join ratings on reviews.id=ratings.rateable_id && ratings.rateable_type="Review" && ratings.user_id <> reviews.user_id',
  :conditions=>'ratings.rating is NULL')