ruby-on-rails – 在Rails上使用计算进行组查询3
发布时间:2020-12-17 04:03:29 所属栏目:百科 来源:网络整理
导读:Rails 3问题. 我有一份Foods食品表,其中包含以下属性: 名字 卡路里(每克) 脂肪(每克) 碳水化合物(每克) 蛋白质(每克) 然后我有一个LoggedFoods表,表示在给定时间吃过的食物.它具有以下属性: food_id number_of_grams_eaten ate_when(datetime) 所以我的问
Rails 3问题.
我有一份Foods食品表,其中包含以下属性: >名字 然后我有一个LoggedFoods表,表示在给定时间吃过的食物.它具有以下属性: > food_id 所以我的问题是我想在一次查询中得到每天(所有日子)消耗的卡路里,脂肪,蛋白质,碳水化合物的总数.我一直在尝试使用新的ActiveRecord查询界面执行此Rails 3并且没有运气.有任何想法吗? 解决方法
这是一个快速的第一步,可能有一些错误,但数字似乎一目了然.另外:我只在sqlite3上测试过,所以其他数据库的结果可能不同(如果SUM或组函数不同)
应用程序/模型/ 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(我显然不知道食物的营养信息) @pizza = Food.create(:name => "pizza",:calories => 500,:fat => 10,:carbs => 20,:protien => 30) @hot_dog = Food.create(:name => "hot dog",:calories => 400,:protien => 30) @apple = Food.create(:name => "apple",:calories => 100,:fat => 1,:carbs => 2,:protien => 3) @banana = Food.create(:name => "banana",: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,:ate_when => Time.now) LoggedFood.create(:food_id => @banana.id,:ate_when => 12.hours.ago) LoggedFood.create(:food_id => @apple.id,:ate_when => 1.day.ago) LoggedFood.create(:food_id => @pizza.id,:ate_when => 2.days.ago) LoggedFood.create(:food_id => @banana.id,:ate_when => 36.hours.ago) LoggedFood.create(:food_id => @hot_dog.id,:ate_when => 50.hours.ago) 然后在控制台中: 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(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} (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
推荐文章
站长推荐
热点阅读