sql - How to avoid this NOT IN clause? -

i have 3 tables:

the scenario

|  users |         | teams_users |      | teams |  --------           -------------        ------- |   id   |         |   user_id   |      |   id  |                    |   team_id   |                     | begin_date  |                    |  end_date   | 

so. if user assigned team, new team_users registry id created, , end_date null.

if user pulled of team, end_date filled current date.

why way?

because there's need keep history of user in team.

the problem

when i'm creating/editing team, want present list of available users team. list should follow these rules:

• the user can't in current team
• the user can on team
• the user can have team_user registry, end_date != null

the solution far

i'm using rails 2.3.14 (don't ask!). solution far is:

#team_id current team  users_on_this_team = teamuser.all(    :conditions => {:team_id => team_id, :end_date => nil} ).collect { |team_user| team_user.user_id }  user.all(:conditions => ["id not in (?)", users_on_this_team] 

which works, feels hacky. i'm trying came better solution, maybe using join, i'm stuck.

any ideias?

you can use arel gem, allow advance query:

ids = teamuser.select("distinct teams_users.user_id")                .where(team_id: team_id, end_date: nil) user.where(user.arel_table[:id].not_in(ids)) 

but guess, better way use specific scope:

class user    belongs_to :team_user     scope :outteam_users, ->{ joins(team_user).where(teamuser.             arel_table[:user_id].not_eq(user.arel_table[:id]) } end 

please note both queries unconformed.