How to get specific data from my tables in Rails 3

Here are my models:

class Food < ActiveRecord::Base
  has_many :lists
  has_many :costs, :through => :lists
end

class List < ActiveRecord::Base #each food can have multiple lists, ordered by date
  belongs_to :food
  has_many :costs, :dependent => :destroy
  accetps_nested_attribute_for :costs, :allow_destroy => true
end

class Cost < ActiveRecord::Base #costs are nested inside of a list
  belongs_to :food
  belongs_to :list
end

Here is my schema (the part you need to see):

create_table "foods", :force => true do |t|
  t.integer  "food_id"
  t.string  "name"
  t.string  "type" # this is where I can choose a 'fruit' or a 'vegetable'
end

create_table "lists", :force => true do |t|
  t.integer "food_id"
  t.integer  "quarter" #this is how lists are ordered
  t.integer  "year"
end 

create_table "costs", :force => true do |t|
  t.integer  "amount" 
  t.integer "list_id"
  t.integer "food_id"
end

What I want to do is to be able to filter down through my tables to show total or average costs based on certain criteria. So if for example I want to know the total or average cost (the :amount attribute from the cost model) of all fruit for a certain period of time (sorted in the list model by :quarter and :year). Is that more clear? Thanks for the feedback so far.

Solution

You need to fix your models first. You have Cost belonging to both List and Food, but no foreign key for either in your migration. In general, if model A :belongs_to model B, the table for model A needs b_id as a foreign key.

Once you've fixed that, since you want an aggregate, you'll have to build a query based off the model that has the value to aggregate - in this case, Cost. You want to limit that to include only those costs associated with a Food with a certain attribute - so use method chaining like this (assuming you're using Rails 3):

# average cost of all fruit
Cost.includes(:food).where('foods.type = ?', 'fruit').average(:amount)

To limit this by year and quarter gets a bit more complicated but works along the same lines, but to give you solid advice on that, you need to fix your models first. I recommend reading up on these two guides:

EDIT

After your edit, try this (untested):

Cost.includes(:food, :list).where('foods.type = ? AND lists.year = ? AND lists.quarter = ?', 'fruit', 2011, 1).average(:amount)