ou.jiayong.name

Hi, this is Jiayong Ou's blog about programming and other things he deems interesting.

He's also on Twitter as @jiayongou and short tidbits of his discoveries on the world wide web are on orly.ch. There are also pages for his other online activies, how to get in touch with him, his projects and about his person.

ActiveRecord, named scopes, select statements and you

ActiveRecord’s named_scopes are awesome, but I recently run into something less awesome about it: It doesn’t merge the :select option of nested scopes.

So let’s look something like that:

class Profile::Business < Profile::Profile
  # ... some code ...

  named_scope(:by_popularity, 
    :select => "profile_profiles.*, count(profile_fans.member_id) AS fan_count",
    # ... some more stuff for the scope
    :order => "fan_count DESC"
  )

  # ... some more code ...
end

And we use the named scope like this:

Profile::Business.by_popularity.find(:all, :select => 'profile_profile.*')

Should work, shouldn’t it? But as you might have guessed, it’s where ActiveRecord is exploding by throwing an ActiveRecord::StatementInvalid exception:

Mysql::Error: Unknown column 'fan_count' in 'order clause':

If you look at the rails log, you should see something like that:

Profile::Business Load (0.017810) SELECT profile_profiles.* FROM `profile_profiles` INNER JOIN [... some more SQL ...]

Yes, my first though was ‘Dude, where’s my SELECT?’. Turns out ActiveRecord doesn’t merge them it just use the last :select it encounters. In my case the workaround was easy by moving the COUNT() from :select to :order:

class Profile::Business < Profile::Profile
  # ... some code ...

  named_scope(:by_popularity, 
    :select => "profile_profiles.*",
    # ... some more stuff for the scope
    :order => "count(profile_fans.member_id) DESC"
  )

  # ... some more code ...
end

Crisis averted in this case, but it still leaves an uneasy feeling. And of course, the documentation says nothing about that.

Oh well… Someone proposed a patch for this problem and let’s hope it makes it into a Rails release.