Includes and Conditions in Named Scope

I was making a change to NewsMilk the other day where I needed to make sure that once a user sees a story, it is not shown to that user again. To do this I added a has many between stories and people, and I wanted to make a nice named scope to handle this. Currently the code that gets new stories looks something like this:

story = viewable.first

where viewable is a named scope that adds the proper conditions to the find. I wanted to make a little something something, like this:

story = viewable.not_viewed(person).first

where the person argument is the person that should not have seen this story before.

My first attempt at this was:

  
named_scope :not_viewed, lambda { |person|
    if person
      {:include=>:viewed_stories, :conditions=>{:person_id=>person.id}}
    else
      {}
    end
  }

Which outputs this sql:

SELECT * 
FROM `stories` 
WHERE (person_id != 1)

ActiveRecord::StatementInvalid (Mysql::Error: Unknown column 'person_id' in 'where clause': ....

This doesn’t work because the rails eager loading optimization stuff takes the query and transforms it into two queries. The first query gets the stories and the second the viewed_stories. Since the person_id column does not exist in the stories table, the sql bombs. So I made one small change, I went back to old school conditions array and used the qualified column name for person_id:

named_scope :not_viewed, lambda { |person|
    if person
      {:include=>:viewed_stories, :conditions=>["viewed_stories.person_id != ?", person.id]}
    else
      {}
    end
  }

Which outputs this sql:

SELECT DISTINCT `stories`.id 
FROM `stories` 
  LEFT OUTER JOIN `viewed_stories` ON viewed_stories.stories_id = stories.id 
WHERE (viewed_stories.person_id != 1) 
ORDER BY published_at desc LIMIT 1

Notice how now Rails is only doing one query with a join instead of two. And for those of you wondering why I check the existence of person in the named scope, it’s just to avoid the join if it is not needed, because without a person there is nothing to where on.