has_many "not something" :through. What conditions should I use?

Question

I have User, Game and GameView. GameView describe what games users have seen. Trouble is I can't figure out what conditions should I use to fetch unviewed games.

class User < ActiveRecord::Base
  has_many :game_views
  has_many :unviewed_games, :through => :game_views, :source => :game, ???what conditions???
end

class GameView < ActiveRecord::Base
  belongs_to :user
  belongs_to :game
end

Answer 1

Why don't you has_many :viewed_games and write a separate method to collect unviewed games? I'd be hesitant to put any more logic in a has_many.

Answer 2

I don't see this as an association because an association is generally something where you have a foreign key pointing to something, but in this case you have no foreign key. I see this as more of an instance attribute which I would do like this:

def unviewed_games
  Game.all(:conditions => ["id NOT IN (SELECT game_id FROM game_views WHERE user_id = ?", self.id])
end

You could do a NOT IN (1,2,3) by querying the viewed games, but that can get very inefficient, very very fast. This is one time I would write out the SQL. I would also do one more thing:

def unviewed_games
  return @unviewed_games if defined(@unviewed_games)
  @unviewed_games = Game.all(:conditions => ["id NOT IN (SELECT game_id FROM game_views WHERE user_id = ?", self.id])
end

That will store it in an instance variable for the length of the request, and save you the multiple database hits. You can do ||=, but if somehow you were to get a nil, then you would still query the database multiple times. Rails should cache, but call me paranoid.

Hope this helps!

Answer 3

You can't express a relationship this way in Rails using has_many :through (or at least not to my knowledge). But you can create a named scope based on :game_views to retrieve the desired objects.

I believe this will do the trick (not tested):

named_scope :unviewed_games, :joins => 'LEFT JOIN game_views ON game_views.game_id = games.id', :conditions => 'game_view_id IS NULL')