CODEWITHSUNDEEP
ruby-on-rails-3models
FattRyan
FattRyan

Reputation: 896

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.

Upvotes: 0

Views: 301

Answers (1)

Thilo
Thilo

Reputation: 17735

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)

Upvotes: 1

Related Questions