ActiveRecord find with association details

Question

Suppose I am doing matchmaking of users and games. I have models containing users and games.

class Game < ActiveRecord::Base
  has_and_belongs_to_many :users 

class User < ActiveRecord::Base
  has_and_belongs_to_many :games

Games can have many users, users can be playing many games. Because of HASBM I have a table called games_users too.

I want to search and find games that are waiting for players, which do not also contain the username of the player (i.e. I don't want to add the same player to a game twice...)

I want something like this:

@game = Game.find_by_status(Status::WAITING_USERS, :condition => "game.users.doesnt_contain('username=player')

But I'm not sure how to do it?

Update:

Using jdl's solution, I got the code to run, but get items that I tried to exclude returned in the results. Here's my test code:

logger.debug "Excluding user: #{@user.id}"
games = Game.excluding_user(@user)
if (games != nil && games.count > 0)
  @game = Game.find(games[0].id)
  games[0].users.each {
   |u|
   logger.debug "returned game user: #{u.id}"
  }
end

(the above code also begs 2 questions.... - how do I get a result of just one game instead of an array, and how to I get a non-readonly version of it; that's why I do the second Game.find...)

And here's the output in the log:

Excluding user: 2
  Game Load (0.3ms)   SELECT `games`.* FROM `games` left outer join games_users gu on gu.game_id = games.id WHERE (gu.game_id is null or gu.user_id != 2) 
  Game Columns (1.0ms)   SHOW FIELDS FROM `games`
  SQL (0.2ms)   SELECT count(*) AS count_all FROM `games` left outer join games_users gu on gu.game_id = games.id WHERE (gu.game_id is null or gu.user_id != 2) 
  Game Load (0.1ms)   SELECT * FROM `games` WHERE (`games`.`id` = 3) 
  games_users Columns (6.8ms)   SHOW FIELDS FROM `games_users`
  User Load (0.9ms)   SELECT * FROM `users` INNER JOIN `games_users` ON `users`.id = `games_users`.user_id WHERE (`games_users`.game_id = 3 ) 
returned game user: 1
returned game user: 2

Answer 1

You could execute your query the way you want and use the slice method to remove the user from the results (but this is not very good performance wise if the user is added to a lot of games already)

sort of like this

   a = [ "a", "b", "c" ]
   a.slice!(1)     #=> "b"
   a               #=> ["a", "c"]

Or write a custom sql query (using find_by_sql and use != user_id to exclude that from the query.

I am not sure if there is a "pure" Rails way to do it in the find itself without using a custom query.

edit:

You could do something like this, pretty "Railsy",

@game = Game.find_by_status(Status::WAITING_USERS, :conditions => ["id NOT IN #{user_id}"])

Or for multiple users, an array

@game = Game.find_by_status(Status::WAITING_USERS, :conditions => ["id NOT IN (?)", [1,2,3]])

Let me know if that works out for you :-)

Answer 2

Named scopes are your friend here.

For example:

class Game < ActiveRecord::Base
  has_and_belongs_to_many :users

  named_scope :for_status, lambda {|s| {:conditions => {:status => s}}}
  named_scope :excluding_user, lambda {|u| {:conditions => ["gu.game_id is null or gu.game_id not in (select game_id from games_users where user_id = ?) ", u.id], :joins => "left outer join games_users gu on gu.game_id = games.id", :group => "games.id" }}
end

This will let you do things like the following:

user = User.first  # Or whoever.
games_in_progress = Game.for_status("playing")
games_in_progress_for_others = Game.excluding_user(user).for_status("playing")
# etc...

Also, since you say that you're new to Rails, you might not realize that these named scopes will also work when you're traversing associations. For example:

user = User.first
users_games_in_waiting = user.games.for_status("waiting")

Answer 3

It might be easier in a two step process.

Step 1 get the list of games the user is involved in:

games_playing = user.games.for_status('playing')

Step 2 get a list of open games for the player:

open_games = Game.for_status('waiting').not_including(games_playing)

Where you have an additional named scope in the Game class:

named_scope :not_including, lambda {|g| { :conditions => ["id not in (?) ", g] }}