title: Rails ActiveRecord how do I find all records where the record is not joined with a particular other record?
body: <p>I want to search for all users (<code>Users</code>) who are not part of group 1 (<code>Groups</code>), joined by <code>UserGroups</code>, i.e. "find all users that do not have a UserGroups record binding them to Group 1."</p>
  
  <p>I can find all users with <code>User.all</code> or find them by name with <code>User.where('name like ?' , '%'+params[:search]+'%')</code></p>
  
  <p>I can even find those that have <strong>no</strong> memberships:</p>
  
  <pre><code>User.includes(:user_groups).where( :user_groups =&gt; { :user_id =&gt; nil } )
  </code></pre>
  
  <p>But how do I say, "find all <code>Users</code> that are not members of group 1?" </p>
  
  <p>UPDATE: here is valid SQL for it:</p>
  
  <pre><code>select * from Users as u where not exists (select 1 from UserGroups where group_id = 1 and user_id = u.id);
  </code></pre>
  
  <p>or, if you prefer the cached <code>NOT IN</code></p>
  
  <pre><code>select * from Users as u where u.id not in (select user_id from UserGroups where group_id = 1);
  </code></pre>
  
  <p>UPDATE:</p>
  
  <p>Only way I found to do it right now is raw SQL, but would love a true ActiveRecord method:</p>
  
  <pre><code>User.where('name like ? and id not in (select user_id from UserGroups where group_id = ?)' , '%'+params[:search]+'%',@group.id)
  </code></pre>