Adding Mysql Columns in Rails Rake
- by Gigg
I have a rake file that does a series of calculations on a database. Basically it adds usage on equipment regularly. At the end of the day it needs to add that days total to a monthly total table and update that same table. i use the following simple concepts:
To get data from the database is pretty simple:
@usages = Usage.find(:all)
time = Time.new
for usage in @usages
sql = ActiveRecord::Base.connection
To insert it into the database:
sql.execute "INSERT (or UPDATE) into usages ## add values and options as per MySQL
But how do I:
take a column from a database,
add all of its values together that have a common value in another column, (i.e. if column x == value y)
and then insert it into another column in another table, say dailyusages?
I have tried these options:
task (:monthly => :environment) do
@dailyusages = Dailyusage.find(:all)
for dailyusage in @dailyusages
sql = ActiveRecord::Base.connection
time = Time.new
device = monthlyusages.device
month = time.month
if device == dailyusages.device ##&& month == dailyusages.month
total = (dailyusage.total.sum.to_i)
@monthlyusages = Monthlyusage.find(:all)
for monthlyusage in @monthlyusages
sql = ActiveRecord::Base.connection
old_total = monthlyusage.total.to_i
new_total = (old_total + total)
sql.execute "UPDATE monthlyusages ( year, month, total, device ) values('#{time.year}', '#{time.month}', '#{total}', '#{dailyusage.device}' )"
end
I obviously have uncommented options and tried all sorts of things. Any help would really save me a load of trouble.
Thanks in advance.
(** BTW - I am new to rails, so go easy on me **)
