Active rails entry: refresh table with login

Question

I am working on a rails project. I have two tables storing data from disparate systems: tasks and projects.

Provided that the projects know the tasks through ref_id, but not in the opposite direction. therefore, as soon as matches are found from the connection, I would like to set task.project_id with the associated project table identifier.

UPDATE FROM task AS t LEFT JOIN projects as p ON t.ref_id = p.ref_id SET t.project_id = p.id 

my question here is: how can I achieve this using active recording in rails?

Answer 1

Task.joins (: project) .update_all ("tasks.project_id = projects.id")

Answer 2

If you use postgres, you can run update_all with "join" from the model, for example:

Task.connection.update_sql("UPDATE tasks AS t SET project_id = p.id FROM projects AS p WHERE t.ref_id = p.ref_id")

Note: the call to update_sql deprecated for Rails 5 and should be switched to update

Since you are sending sql directly, you need to make sure that you use the syntax appropriate for your database.

Answer 3

You can do this by passing your sql directly

 Task.connection.update_sql("UPDATE FROM task AS t LEFT JOIN projects as p ON t.ref_id = #{p.ref_id} SET t.project_id = #{p.id}")