named scope based on number of associated records

I am doing some basic sql logic and I want to use a named scope. I'm trying to find out how many members of a season have also participated in another season (i.e. they are returning members).

class Season
  has_many :season_members
  has_many :users, :through => :season_members

  def returning_members
    users.select { |u| u.season_members.count > 1 }
  end
end

class SeasonMember
  belongs_to :season
  belongs_to :user
end

class User
  has_many :season_members
end

Is it possible to use :group and friends to rewrite the returning_members method as a scope?

I happen to be using Rails 2.3 but I'll also accept solutions that rely on newer versions.

Not sure if you really want to put this scope on Season, since that would imply that you are looking for Seasons which have repeat users. But, I assume you want Users that have repeat seasons. With that assumption, your scope would be:

class User < ActiveRecord::Base
 scope :repeat_members, 
    :select=>"users.*, count(season_members.season_id) as season_counter",
    :joins=>"JOIN season_members ON season_members.user_id = users.id",
    :group=>"users.id",
    :having=>"season_counter > 1"
end

which would result in the following query:

 SELECT users.*, count(season_members.season_id) as season_counter FROM "users" JOIN season_members ON season_members.user_id = users.id GROUP BY users.id HAVING season_counter > 1

Confirmed with: Rails 3.1.3 and SQLite3