Group Query with Calculations on Rails 3
Rails 3 problem. I have a Foods table of foods with the following attributes:
- name
- calories (per gram)
- fat (per gram)
- carbs (per gram)
- protein (per gram)
I then have a LoggedFoods table representing a food that has been eaten at a given time. It has the 开发者_C百科following attributes:
- food_id
- number_of_grams_eaten
- ate_when (datetime)
So the problem I have is that I'd like to get the total number of calories, fat, protein, carbs consumed per day (for all days) in one query. I've been trying to do this Rails 3 using the new ActiveRecord query interface and had no luck. Any ideas?
Here's a quick first pass at this, there may be some bugs, but the numbers seem right at a glance. Also: I only tested this on sqlite3, so results on other databases may be different (in case the SUM or group functions are different)
app/models/logged_food.rb
class LoggedFood < ActiveRecord::Base
belongs_to :food
def self.totals_by_day(date)
start_time = Time.parse(date).beginning_of_day
end_time = Time.parse(date).end_of_day
t = LoggedFood.arel_table
totals = LoggedFood.
where(t[:ate_when].gteq(start_time)).
where(t[:ate_when].lteq(end_time)).
joins(:food).
select("SUM(calories * grams_eaten) as total_calories").
select("SUM(fat * grams_eaten) as total_fat").
select("SUM(carbs * grams_eaten) as total_carbs").
select("SUM(protien * grams_eaten) as total_protien")
return nil if totals.empty?
{
:total_calories => totals.first.total_calories,
:total_fat => totals.first.total_fat,
:total_carbs => totals.first.total_carbs,
:total_protien => totals.first.total_protien
}
end
end
db/seeds.rb
(I obviously have no idea of the nutritional information of food)
@pizza = Food.create(:name => "pizza", :calories => 500, :fat => 10, :carbs => 20, :protien => 30)
@hot_dog = Food.create(:name => "hot dog", :calories => 400, :fat => 10, :carbs => 20, :protien => 30)
@apple = Food.create(:name => "apple", :calories => 100, :fat => 1, :carbs => 2, :protien => 3)
@banana = Food.create(:name => "banana", :calories => 100, :fat => 2, :carbs => 4, :protien => 6)
LoggedFood.create(:food_id => @pizza.id, :grams_eaten => 10, :ate_when => Time.now)
LoggedFood.create(:food_id => @apple.id, :grams_eaten => 10, :ate_when => Time.now)
LoggedFood.create(:food_id => @banana.id, :grams_eaten => 10, :ate_when => 12.hours.ago)
LoggedFood.create(:food_id => @apple.id, :grams_eaten => 10, :ate_when => 1.day.ago)
LoggedFood.create(:food_id => @pizza.id, :grams_eaten => 10, :ate_when => 2.days.ago)
LoggedFood.create(:food_id => @banana.id, :grams_eaten => 10, :ate_when => 36.hours.ago)
LoggedFood.create(:food_id => @hot_dog.id, :grams_eaten => 10, :ate_when => 2.days.ago)
LoggedFood.create(:food_id => @banana.id, :grams_eaten => 10, :ate_when => 50.hours.ago)
Then in the console:
ree-1.8.7-2010.02 > LoggedFood.totals_by_day("2010-08-27")
LoggedFood Load (0.2ms) SELECT SUM(calories * grams_eaten) as total_calories, SUM(fat * grams_eaten) as total_fat, SUM(carbs * grams_eaten) as total_carbs, SUM(protien * grams_eaten) as total_protien FROM "logged_foods" INNER JOIN "foods" ON "foods"."id" = "logged_foods"."food_id" WHERE ("logged_foods"."ate_when" >= '2010-08-27 04:00:00.000000') AND ("logged_foods"."ate_when" <= '2010-08-28 03:59:59.999999') LIMIT 1
=> {:total_fat=>130, :total_protien=>390, :total_calories=>7000, :total_carbs=>260}
ree-1.8.7-2010.02 > LoggedFood.totals_by_day("2010-08-26")
LoggedFood Load (0.3ms) SELECT SUM(calories * grams_eaten) as total_calories, SUM(fat * grams_eaten) as total_fat, SUM(carbs * grams_eaten) as total_carbs, SUM(protien * grams_eaten) as total_protien FROM "logged_foods" INNER JOIN "foods" ON "foods"."id" = "logged_foods"."food_id" WHERE ("logged_foods"."ate_when" >= '2010-08-26 04:00:00.000000') AND ("logged_foods"."ate_when" <= '2010-08-27 03:59:59.999999') LIMIT 1
=> {:total_fat=>30, :total_protien=>90, :total_calories=>2000, :total_carbs=>60}
精彩评论