Group Query with Calculations on Rails 3

Question

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 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?

Answer 1

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}