Using Postgres, calculate the number of associations
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 with it, but I need this to be done using an active record, not through each Foos, for example
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 cache counter to foo, but I just want to know how I can do this without it. Thank!
source to share
SQL does not allow aliases in WHERE clauses, but only column names.
Alternatively, you can try one of the following:
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 is select
interpreted in ruby as it contains the & 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
source to share
The 1st solution in the currently accepted answer is ineffective as the linked subquery is executed for every row of the table foos
. Using unions is the best approach for these scenarios.
The second solution in the current accepted answer doesn't work for foos
without bars
.
For example: a new product without any orders.
You have to use outer join LEFT to solve this problem. In addition, comparison comparisons can be performed using a sentence having
. Thus, the entire operation is processed in the database.
def self.good_with_spots_left
joins("LEFT OUTER JOIN bars bars ON bars.foo_id = foos.id").
group('bars.foo_id').
having("foos.max_spots > COUNT(COALESCE(bars.foo_id, 0))")
end
Note:
The command COALESCE
returns the first non-zero input. This command is compatible with SQL92, so it will work across databases.
Why do we use COALESCE
?
LEFT OUTER JOIN
returns the NULL
value for bars.foo_id
if there is no match bars
for foo
. The SQL operation COUNT
doesn't like the values NULL
in the set, so we'll convert the value NULL
to 0
.
source to share