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.
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:
- http://guides.rubyonrails.org/association_basics.html
- http://guides.rubyonrails.org/active_record_querying.html
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)
精彩评论