Includes and Conditions in Named Scope

I was making a change to "NewsMilk":http://newsmilk.com 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)

And this error:

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.

1 Comment

Lourens Naude said on November 11, 2008

General best practice being to always use the full table.field syntax.

It’s fairly common in performance reviews to optimize certain sections with :include, which WILL blow up in the manner you mentioned in the post.Same with custom joins.

Initial additional keystrokes being king to the WTF moment later, in a likely stressful and timestrapped scenario.