Includes and Conditions in Named Scope

Written by on Nov 10 2008

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:

namedscope :notviewed, lambda { |person| if person {:include=>:viewedstories, :conditions=>{:personid=>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:

namedscope :notviewed, lambda { |person| if person {:include=>:viewedstories, :conditions=>[“viewedstories.person_id != ?”, person.id]} else {} end }

Which outputs this sql:

SELECT DISTINCT stories.id FROM stories LEFT OUTER JOIN viewed_stories ON viewedstories.storiesid = stories.id WHERE (viewedstories.personid != 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.

Meet
Steven

Hi I'm Steven,

I wrote the article you're reading... I lead the developers, write music, used to race motorcycles, and help clients find the right features to build on their product.

Get Blog Updates