CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
Horacio
Horacio

Reputation: 2965

Activerecord counting with join

I have 2 activerecord relation

class Proxy  
   # capacity integer  
   # country string  
   has_many :configs  
end

class Config  
   belongs_to :proxy  
   # proxy_id  
   # port integer 
end

I want to find in a activerecord query all proxies for a specific country, where the capacity is less than his configs and order them by configs counter.

I mean I was trying to do something like (pseudo activrecord example)

Proxy.joins(:configs).where(country: country)
                    .where("capacity > ?", :configs.count)
                    .order_by(:configs.count)

I guess that maybe I'm tangled with my problem and it has an easy solution.

Thanks in advance!

Upvotes: 0

Views: 32

Answers (2)

R. Sierra
R. Sierra

Reputation: 1204

Try using a :counter_cache in the associations

class Proxy  
 # configs_count integer
 has_many :configs  
end

class Config  
 belongs_to :proxy, counter_cache: true
end

Then you could do something like:

Proxy.where(country: country)
     .where("capacity > ?", :configs_count)
     .order_by(:configs_count)

Upvotes: 1

Pavel Mikhailyuk
Pavel Mikhailyuk

Reputation: 2877

It is standard SELECT ... JOIN ... GROUP BY ... HAVING ... ORDER SQL task:

Proxy.
  joins(:configs).
  group(:id).
  having('proxies.capacity > count(configs.id)').
  order('count(configs.id)')

Upvotes: 1

Related Questions