title: Rails: How to Select Records Which Don't Have a Specific Related (associated) Object (SQL EXISTS brief how-to)
body: <p>Let's assume we have users:</p>
  
  <pre><code>class User &lt; ActiveRecord::Base
    has_many :connections
    has_many :groups, through: :connections
  end
  </code></pre>
  
  <p>And groups:</p>
  
  <pre><code>class Group &lt; ActiveRecord::Base
    has_many :connections
    has_many :users, through: :connections
  end
  </code></pre>
  
  <p>Basically, standard many-to-many connection:</p>
  
  <pre><code>class Connection
    belongs_to :user
    belongs_to :group
  end
  </code></pre>
  
  <p>What I intend to do is:</p>
  
  <ul>
  <li>Select only users who don't belong to given set of Groups (groups with ids <code>[4,5,6]</code>)</li>
  <li>Select only users who belong to one set of Groups (<code>[1,2,3]</code>) and don't belong to another (<code>[4,5,6]</code>)</li>
  <li>Select only users who don't belong to a Group</li>
  </ul>
  
  <p>Also, I don't want to:</p>
  
  <ul>
  <li>Fetch a lot of data from database to manipulate it with Ruby code. I know that will be inefficient in terms of CPU and memory (Ruby is much slower than any commonly used DB engine, and typically I want to rely on DB engine to do the heavy lifting)</li>
  <li>I tried queries like <code>User.joins(:group).where(group_id: [1,2,3]).where.not(group_id: [4,5,6])</code> and they return wrong results (some users from the result set belong to groups 4,5,6 <em>as well as</em> 1,2,3)</li>
  <li>I don't want to do <code>join</code> merely for the sake of only checking for existence, because I know that that is a pretty complex (i.e. CPU/memory-intensive) operation for DB</li>
  </ul>