How would I construct this join with a count condition in the joined table?

Question

I'm trying to find the 'open' games, which are games that have a player slot open. Games are created with num_players, which represents the max player capacity for that game.

Games have_many players.

I tried the following

Game.find(:all, :include => :players, :group => 'players.game_id', :conditions => ['players.count(*) < games.num_players'])

but it didn't like the count(*) in the conditions

Answer 1

The short answer is:

Game.find(:all, :include => :players, :group => 'players.game_id', 
  :select => "games.*, COUNT(players.id) AS players_count", 
  :conditions => "players_count < games.num_players"
  )

However that's not very DRY. You might find it easier in the long run to set that relationship up with a counter cache. See this railscast for more detailed info on the how and why.

To setting up the counter cache is a simple three step process.

1. Add a players_count integer column to Game.
2. Update players_count for all your existing game records.
3. Add :counter_cache => true to the belongs_to :game declaration.

Then to find all open games:

Game.find(:all, :conditions => "games.players_count < games.num_players")

Bonus points for making that a named scope:

class Game < ActiveRecord::Base
  has_many :players

  named_scope :open, :conditions => "games.players_count < games.num_players"
  named_scope :type, lambda {|type| 
    {:conditions => {:type => type}}
  }
  named_scope :baseball, :conditions => {:type => "baseball"}
end

Game.open # => Returns all open games.
Game.baseball.open # => Returns all open baseball games.
Game.open.type("football") # => Returns all open foot ball games.