title: SQL LEFT JOIN value NOT in either join column
body: <p>I suspect this is a rather common scenario and may show my ineptitude as a DB developer, but here goes anyway ...</p>
  
  <p>I have two tables: <strong>Profiles</strong> and <strong>HiddenProfiles</strong> and the <strong>HiddenProfiles</strong> table has two relevant foreign keys: <em>profile_id</em> and <em>hidden_profile_id</em> that store <em>id</em>s from the <strong>Profiles</strong> table.</p>
  
  <p>As you can imagine, a user can <strong>hide</strong> another user (wherein his profile ID would be the <em>profile_id</em> in the <strong>HiddenProfiles</strong> table) or he can be <strong>hidden</strong> by another user (wherein his profile ID would be put in the <em>hidden_profile_id</em> column). Again, a pretty common scenario.</p>
  
  <p><strong>Desired Outcome:</strong><br>
  I want to do a join (or to be honest, whatever would be the most efficient query) on the Profiles and HiddenProfiles table to find all the profiles that a given profile is both not hiding AND not hidden from.</p>
  
  <p>In my head I thought it would be pretty straightforward, but the iterations I came up with kept seeming to miss one half of the problem. Finally, I ended up with something that looks like this:</p>
  
  <pre><code>SELECT "profiles".* FROM "profiles"
  LEFT JOIN hidden_profiles hp1 on hp1.profile_id = profiles.id and (hp1.hidden_profile_id = 1)
  LEFT JOIN hidden_profiles hp2 on hp2.hidden_profile_id = profiles.id and (hp2.profile_id = 1)
  WHERE (hp1.hidden_profile_id is null) AND (hp2.profile_id is null)
  </code></pre>
  
  <p>Don't get me wrong, this "works" but in my heart of hearts I feel like there should be a better way. If in fact there is not, I'm more than happy to accept that answer from someone with more wisdom than myself on the matter. :)</p>
  
  <p>And for what it's worth these are two RoR models sitting on a Postgres DB, so solutions tailored to those constraints are appreciated.</p>
  
  <p><hr>
  Models are as such:</p>
  
  <pre><code>class Profile &lt; ActiveRecord::Base
      ...
      has_many :hidden_profiles, dependent: :delete_all
  
      scope :not_hidden_to_me, -&gt; (profile) { joins("LEFT JOIN hidden_profiles hp1 on hp1.profile_id = profiles.id and (hp1.hidden_profile_id = #{profile.id})").where("hp1.hidden_profile_id is null") }
      scope :not_hidden_by_me, -&gt; (profile) { joins("LEFT JOIN hidden_profiles hp2 on hp2.hidden_profile_id = profiles.id and (hp2.profile_id = #{profile.id})").where("hp2.profile_id is null") }
      scope :not_hidden, -&gt; (profile) { self.not_hidden_to_me(profile).not_hidden_by_me(profile) }
      ...
  end
  
  class HiddenProfile &lt; ActiveRecord::Base
      belongs_to :profile
      belongs_to :hidden_profile, class_name: "Profile"
  end
  </code></pre>
  
  <p>So to get the profiles I want I'm doing the following:</p>
  
  <pre><code>Profile.not_hidden(given_profile)
  </code></pre>
  
  <p>And again, maybe this is fine, but if there's a better way I'll happily take it.</p>