-1

Need to update a field within my mysql db. Would like to use the following query within rails: 

update users set exported = TRUE where id = #{name[12]}

Currently, have the following:

db = Mysql2::Client.new( :host => 'localhost', :username => 'username', :password => 'password', :database => 'database')

results = db.query("select * from users where start_date is not NULL AND exported = 0").each(:as => :array)

results.each do |name|

db.query("update users set exported = TRUE where id = #{name[12]}")

end

index 12 is equal to the UID.

Atsu Mori
  • 11
  • 5

2 Answers2

1

I would suggest that you use ActiveRecord for database calls when you are using Ruby on Rails.

I assume that you have at least a basic User model like this:

class User < ApplicationRecord
end

With an User model like that your query could look like this:

User.where.not(start_date: nil).where(exported: false).update_all(exported: true)
spickermann
  • 100,941
  • 9
  • 101
  • 131
0

If you're using rails, I'd suggest using ActiveRecord. Your update will be:

@users = User.find(:all, :where => 'start_date is not null and exported = 0 and id = ?', name[12])
@users.each{ |user|
    user.exported = true
    user.save!
}

Hope that helps! Leave a comment if you need further help.

hd1
  • 33,938
  • 5
  • 80
  • 91
  • 1
    That `find(:all)` seems a bit old. `User.where('start_date != NULL AND exported = 0 AND id = ?', name[12]).update_all(exported: true)` should work at least for Rails 4+. – Sebastián Palma Apr 05 '18 at 23:25
  • True, OP didn't indicate what version he was using. That query works in console. – hd1 Apr 06 '18 at 01:58
  • 1
    `find(:all)` is deprecated since Rails 3.2. I doubt that the OP is using such an old version of Rails. – spickermann Apr 06 '18 at 06:45
  • Question: Can we still use activerecord even if we output data to an external mysql db? – Atsu Mori Apr 25 '18 at 14:52
  • @Arjae_t this is a separate question. If you create a chat with me, I'll look into it. Alternatively, ask a new question. It looks as though there is an [establish_connection](https://apidock.com/rails/ActiveRecord/ConnectionHandling/establish_connection) method. This should get you started. – hd1 Apr 25 '18 at 18:58