Using Postgres, calculate count of association

Question

I have 2 models

class Foo < ActiveRecord::Base
  # columns are
  # max_spots

  has_many :bars
end

class Bar < ActiveRecord::Base
  # columns are
  # a_id

  belongs_to :foo
end

I need to get all Foos whose max_spots is greater than the number of bars associated to it but I need it to be done via active record and not by going through each Foos like

class Foo
  #bad
  def self.bad_with_spots_left
    all.select do |foo|
      foo.max_spots - foo.bars.count > 0
    end 
  end

  #good but not working
  def self.good_with_spots_left
    joins(:bars).select('COUNT(bars.id) AS bars_count').where('max_spots - bars_count > 0')
  end
end

I know I can just add a counter cache to foo, but just want to know how I can do this without that. Thanks!

Answer 1

SQL does not allow to use aliases in WHERE clauses but only column names.

As an alternative, you can try one of those those :

In pure SQL

def self.good_with_spots_left
  where('foos.max_spots > (SELECT Count(*) FROM bars WHERE bars.a_id = foos.id)')
end

or, with a bit or ruby (the second select is interpreted in ruby as it contains a &block)

def self.good_with_spots_left
  joins(:bars).select('foos.*', COUNT(bars.id) AS bars_count').group('bars.a_id').select{|foo| foo.max_spots > foo.bars_count}
end