CODEWITHSUNDEEP
ruby-on-railsactiverecordgroup-byarel
Nathan
Nathan

Reputation: 597

Group Query with Calculations on Rails 3

Rails 3 problem. I have a Foods table of foods with the following attributes:

I then have a LoggedFoods table representing a food that has been eaten at a given time. It has the following attributes:

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?

Upvotes: 4

Views: 2674

Answers (1)

Dan McNevin
Dan McNevin

Reputation: 22336

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}

Upvotes: 12

Related Questions