CODEWITHSUNDEEP
ruby-on-railsactiverecord
macoughl
macoughl

Reputation: 611

Update all records in a new table using ActiveRecord

I have a table with 20,000+ rows. I keep a record of each client's daily stats from an ad campaign in the stats column. It would be easier to call up the data for certain requests if I had the totals for Conversions and Spend calculated already in separate columns so I've added them.

Google_Records schema - 

create_table "google_records", :force => true do |t|
 t.string   "user_id"
 t.string   "date"
 t.text     "stats"
 t.text     "account_name"
 t.datetime "created_at",                                                        :null => false
 t.datetime "updated_at",                                                        :null => false
 t.decimal  "total_cost",        :precision => 12, :scale => 2, :default => 0.0, :null => false
 t.integer  "total_conversions",                                :default => 0,   :null => false
end

add_index "google_records", ["date"], :name => "index_google_records_on_date"

Moving forward as records are created those columns will be populated. However, I'm trying to figure out the best way to update the records that are already in the table.

Stats is a hash and I can get the total for one record like so:

user = User.find(6)
GoogleRecord.where(user_id: user).where(date: "20140328").map {|m| m.stats}.map{ |s| s.map{ |key,        value| value[:cost] || 0 } }.map {|m| m.inject(:+)}.compact.reduce(&:+)
=> 660.26

I'm currently thinking of writing a migration that could update all the tables at once. Is this possible?

I'm having trouble getting the code to execute properly for even one user (although I do feel as though this may be an improper use of update_all)

("20140320".."20140323").each do |d|
  user = User.find(6)
  totalcost = GoogleRecord.where(user_id: user).where(date: d).map {|m| m.stats}.map{ |s| s.map{ |key, value| value[:cost] || 0 } }.map {|m| m.inject(:+)}.compact.reduce(&:+)
  GoogleRecord.where(user_id: user).where(date: d).update_all(:total_cost => totalcost)
end

Am I going in the right direction conceptually?

Update***

While trying to implement suggestions I realized not all users have these stats. If I can get it working for just one user I'll be able to rewrite it for all users that have stats. I believe I'm close to getting it to work for one user but I can't figure out where I'm messing up this each block.

Here is what I have: 

user = User.find(6)
("20140320".."20140323").each do |d|
google_record = GoogleRecord.where(user_id: user).where(date: d)
total_cost = google_record.map {|m| m.stats.map{ |key, value| value[:cost] || 0 }}.compact.reduce(&:+) || 0
google_record.update_all(:total_cost => total_cost)
end

I'm now getting the following error that suggests this block is trying to insert the results of all records' total_cost into :total_cost but after rewriting this a few times I can't get it so that each separate total_cost calculation goes into the corresponding :total_cost column

←[1m←[36mGoogleRecord Load (2.0ms)←[0m  ←[1mSELECT "google_records".* FROM "google_records"  WHERE "google_records"."user_id" = 6 AND "google_records"."date" = '20140320'←[0m
←[1m←[35mSQL (2.0ms)←[0m  UPDATE "google_records" SET "total_cost" =  258.92,35.82,18.58,47.78,1.42,0.0,0.0,0.0,0.0,0.0,0.44,82.07,2.19,5.87,0.0,0.0 WHERE   "google_records"."user_id" = 6 AND "google_records"."date" = '20140320'
ActiveRecord::StatementInvalid: SQLite3::SQLException: near "35.82": syntax error: UPDATE "google_records" SET "total_cost" = 258.92,35.82,18.58,47.78,1.42,0.0,0.0,0.0,0.0,0.0,0.44,82.07,2.19,5.87,0.0,0.0 WHERE "google_records"."user_id" = 6 AND "google_records"."date" = '20140320'

Upvotes: 1

Views: 1965

Answers (2)

LuiGi
LuiGi

Reputation: 220

I thing that correct way is write it to the migration script. I edit your code and migration script can be look like this bellow:

# code in migration

class HandleGoogleRecordsToUsers < ActiveRecord::Migration

    def up
        User.find_each do |user|
            ("20140320".."20140323").each do |d|
                google_record = GoogleRecord.where(user_id: user).where(date: d)
                totalcost = total_cost(google_record)
                google_record.update_all(:total_cost => totalcost)
            end
        end 
    end

    def down
        raise ActiveRecord::IrreversibleMigration.new "Dont allowed to migrate down."
    end

    def total_cost(google_record)
        # you can write it inside begin and rescue to return 0 if data in hash was incorrect
        result = 0
        begin
            result = google_record.map {|m| m.stats}.map{ |s| s.map{ |key, value| value[:cost] || 0 } }.map {|m| m.inject(:+)}.compact.reduce(&:+)
        rescue => e
            Rails.logger.error("google_record #{google_record.inspect} doesnt count total cost")
            result = 0  
        end
        result
    end

end

Upvotes: 0

nickcen
nickcen

Reputation: 1692

you can take a look at the api doc. http://apidock.com/rails/ActiveRecord/Base/update_all/class

GoogleRecord.update_all(['total_cost = ?', totalcost], ['user_id = ? and date = ?', user.id, d])

Upvotes: 1

Related Questions