how to describe negative find condition in has_many

I have a Teams that has_many Players.

I need to get the list of Teams that have no active players (whether
they have 0 players or they have a lot of players all of whom are non-
active).

Can't figure out the conditions for doing this other than subtracting
the ones with active players from the list of all teams. The issue is
that I don't want teams with *some* non-active players, I need the
teams which have only non-active players.

Here's how I'm doing it now. I assume that there's a better way so
will appreciate seeing the magic.

Thanks,

-Chris

Player < AR::Base

  validates_inclusion_of :status, :in =>
[:active, :inactive, :injured, :dead]

end

Team < AR::Base

  has_many :players

  named_scope :with_active_players, {
     :include => :players,
     :conditions => ["players.status = ?", "active"],
     :group => "teams.id", # no dups
     :order => 'teams.name'
  }

  def self.without_active_players
     (Team.find(:all) - Team.with_active_players).sort_by { |t|
t.name }
  end

end

Try:

named_scope :without_active_players, :conditions => “NOT EXISTS(SELECT * FROM players WHERE team_id=teams.id AND status=‘active’)”, :order => “name”

Does this work?

Lasse

He shoots. He scores!

Thanks much for the insight.

-Chris