CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-4has-many
Kevek
Kevek

Reputation: 2554

Flatten a has_many association and get an attribute sum

I would like to understand the most efficient way to flatten some has_many associations, and subsequently get the sum of some attribute on the child associations. Suppose you have the following data structures:

class Restaurant < ActiveRecord::Base
    has_one :address
    has_many :menu_items
end

class Address < ActiveRecord::Base
    belongs_to :restaurant
end

class MenuItem < ActiveRecord::Base
    belongs_to  :restaurant
end

Say that MenuItem has the attribute "cost", and Address has the attribute "zip_code". What I would like to do is maybe find all restaurants in the zipcode '10101' and get the sum of their menu item's cost attributes. Say that I want to be able to show the average cost of restaurants' offerings in an area.

I think there are quite a few brute force-y ways to do this, but I know there should be something better. For example, in C#/LINQ if I had a similar set of data structures it would be easy to write:

var sum=restaurants.Where(r => r.zip_code==10101).SelectMany(r => r.MenuItems).Sum(mi => mi.Cost);

The best I have come up with, but that feels wrong to me, is:

def thing_I_dont_know_how_to_do
    find_zip=Address.where(:zip_code => zip_code)
    restaurants=Restaurant.joins(:address, :menu_items).merge(find_zip)
    restaurant_ids=restaurants.map(&:id)

    sum=MenuItems.sum(:cost, :conditions => {:restaurant_ids => venue_ids})
end

Can anyone help me improve upon this?

Upvotes: 0

Views: 786

Answers (1)

Baldrick
Baldrick

Reputation: 24340

You can do it with a single SQL request which selects all the menu items of restaurants in zip code 10101, and sums all their cost:

MenuItem.joins(restaurant: :address).where(addresses: { zip_code: '10101' }).sum('cost')

Upvotes: 2

Related Questions