How to create a named count association for preloading

Let's say I have a site for Companys and their Artists who create Songs.

• A Company has many Artists.


• An Artist belongs to a Company.


• An Artist has many Songs.


• A Song belongs to an Artist.

I want to create a page that lists all of the Artists for a given Company and a count for the number of songs they've created in the last 7 days, sorted by the song count.

So I've written the following association for Artist:

has_many    :seven_day_songs,
            :class_name => 'Song',
            :conditions => ['songs.created_at > ?', 7.days.ago]

I'd like the eager load or preload these when a user visits a Company show page:

company_artists = @company.artists.preload(:seven_day_songs, :other_things).all

And what I end up doing is calling sort! and sorting by artist.seven_day_songs.length. Then that collection is passed onto the view. (I figured doing the sort in Ruby was easier than writing a long relation to sort the objects correctly in psql.)

This method of preloading seems wasteful. I'm preloading all the Song objects unnecessarily; I don't use the Song object in anyway except to note its existence. Ideally, :seven_day_songs should really be a count:

has_many    :seven_day_song_count,
            :class_name => 'Song',
            :select => 'count(*) as seven_day_song_count',
            :group => 'songs.artist_id',
            :conditions => ['songs.created_at > ?', 7.days.ago]

Note that I'm using the has_many association because has_one doesn't allow grouping.

But then the preload call doesn't work. I get an error about undefined method 'each' for nil:NilClass. I seem to lose the Artists' other attributes (:name, for example, is missing) that I need in order to display the Artist's information (real name, performing name, etc.).

So, how do I accomplish what I've set out to do? Here are my main goals:

1. Preload the song count on the Artist object in one query


2. Use the song count to sort the company artists without running into the N+1 queries problem because step 1 was completed

Can anyone help me out? I'd like to avoid writing custom SQL. I feel like this should be possible without too much customization...

I'm running Rails 3.2, by the way.