title: Create a scope in rails based on HABTM association count
body: <p>I'm trying to create a rails scope based on the count of a model's HABTM assocation, but I'm struggling with the SQL. </p>
  
  <p>I want Match.open to return matches with less than two users. I also have Match.upcoming, which returns matches with a 'future_date' in the future, which is working well. </p>
  
  <p>My code:</p>
  
  <pre><code>class Match &lt; ActiveRecord::Base
    has_and_belongs_to_many :users
  
    scope :open, joins('matches_users').
        select('*').
        group('matches.id').
        having('count(matches_users.user_id) &lt; 2')
  
  
  scope :upcoming, lambda {
      where("proposed_date between ? and ?", Date.today, Date.today.next_month.beginning_of_month)
  }
  </code></pre>
  
  <p>I'm currently getting the error:</p>
  
  <pre><code>SQLite3::SQLException: no such column: matches_users.user_id: SELECT * FROM "matches" matches_users GROUP BY matches.id HAVING count(matches_users.user_id) &lt; 2
  </code></pre>
  
  <p>ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: matches_users.user_id: SELECT * FROM "matches" matches_users GROUP BY matches.id HAVING count(matches_users.user_id) &lt; 2</p>
  
  <p>I'm currently achieving this with a class method:</p>
  
  <pre><code>def self.open
      self.select{|match| match.users.length &lt; 2}
  end
  </code></pre>
  
  <p>Which works, but I'd really like to move this into a scope for speed, and so that I can chain the scopes like Match.open.upcoming.</p>
  
  <p>What am I doing wrong here? What's the correct way to do this? Any help would be appreciated.</p>