开发者

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)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜