title: How to filter based on a joined tables relations?
body: <p>I don't write much raw SQL so I apologize in advance. Most of my necessary queries resolve within the framework's methods &amp; active record. Hopefully I can explain my question well: I have 3 models:</p>
  
  <pre class="lang-rb prettyprint-override"><code>class Home &lt; ApplicationRecord
  
  has_and_belongs_to_many :users
  # has an attribute "max_number" with the maximum number of users allowed to belong.
  </code></pre>
  
  <pre class="lang-rb prettyprint-override"><code>class User &lt; ApplicationRecord
  
  has_and_belongs_to_many :homes
  has_many :roles, as: :roleable
  
  </code></pre>
  
  <pre><code>class Role &lt; ApplicationRecord
  
  belongs_to :roleable, polymorphic: true
  # has an attribute "name" with the name of the role
  </code></pre>
  
  <p>I want to fetch all of the homes that have users with any role name except for "owner" and with a max # of 2 users associated. I've tried a number of things similar to this query:</p>
  
  <pre class="lang-rb prettyprint-override"><code>Home.joins(user: :roles).group('user.id').having('COUNT(CASE WHEN roles.name != "owner" THEN 1 END) &gt; max_number')
  </code></pre>
  
  <p>But this does not yield the intended result. This seems to includes records that already have 2 users belonging, and does not seem to be impacted by the <code>roles.name</code> portion.</p>
  
  <p>My wording may be confusing: I essentially need to be able to count the number of roles without name "owner" of a user and fetch homes based on that being less than 2.</p>
  
  <p>Any pointers would be greatly appreciated, thanks!</p>