Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find all objects that have greater than x of a certain association

I have many Farms and each farm has many animals. I need to find every farm that has more than 5 animals.

I need something along the lines of this...:

Farm.where(animals.count > 5)  

Update/Answer:

Farm.joins(:animals).group("farm_id").having("count(farm_id) > 5")
like image 877
Corinne Avatar asked Apr 16 '15 04:04

Corinne


2 Answers

Try:

Farm.joins(:animals).group("farm.id").having("count(animals.id) > ?",5)

Ref: https://stackoverflow.com/a/9370734/429758

like image 75
Prakash Murthy Avatar answered Nov 17 '22 10:11

Prakash Murthy


Consider implementing counter_cache on Farm -> Animal

class Farm < ActiveRecord::Base
  has_many :animals
end

class Animal < ActiveRecord::Base
  belongs_to :farm, counter_cache: true
end

Don't forget to add animals_count (integer) to the farms table.

class AddAnimalCounterCacheToFarm < ActiveRecord::Migration
  def up
    add_column :farms, :animals_count, :integer
    # if you need to populate for existing data
    Farm.reset_column_information
    Farm.find_each |farm|
      farm.update_attribute :animals_count, farm.animals.length
    end
  end

  def down
    remove_column :farms, :animals_count
  end
end

To find Farms with 5 or more Animals

Farm.where("farms.animals_count >= 5")
like image 38
messanjah Avatar answered Nov 17 '22 09:11

messanjah