update_all method not working for associated records

Question

I have two models and the association between them is has_and_belongs_to_many. I need to refactor the code as it causes n + 1 queries.

Problem: n+1 queries

1. Task

    class Task < ActiveRecord::Base
      has_and_belongs_to_many :user_groups
    end

2. UserGroup

    class UserGroup < ActiveRecord::Base
      has_and_belongs_to_many :tasks
    end

Requirement:

Assign Tasks to user_groups

Previous Controller Code:

task_ids.each do |task_id|
 find(task_id).update_attributes! user_group_ids: params[:user_group_ids], release_date: params[:release_date]
end

My Attempt:

tasks = Task.where(id: task_ids)
tasks.update_all(user_group_ids: params[:user_group_ids], release_date: params[:release_date])

Error:

ActiveRecord::StatementInvalid (PG::UndefinedColumn: ERROR: column "user_group_ids" of relation "tasks" does not exist

Query generated:

SQL (0.6ms) UPDATE "tasks" SET "user_group_ids" = 4, "release_date" = '2017-04-27 07:40:26.525357' WHERE "tasks"."deleted_at" IS NULL AND "tasks"."id" = 47394

Please let me know how to do it with update_all

Answer 1

Why did you have to create the UserGroup model? That's not the way we use a habtm relationship.

And your model Task, in the middle of it all, kind of confused me. What do you need to do? I suspect that you don't have a habtm relationship in Task.

For the error you're getting, it's totally understandable. You do not have an attribute (column) called user_group_ids in the tasks table. And also, for a habtm relationship, this attribute user_group_ids seems misspelled to me.

Anyway, for a habtm relationship, if your User has many Groups and a Group can have many Users, you're in the right path, however for Ruby on Rails there's two ways you can accomplish that.

1. If you don't need to manage other data (relationship attributes) in the relationship table:

   • Then you'll need only the table groups_users, in your case. Rails will look for habtm relationship table in alphabetic order.

2. If your groups_users relationship holds any other data than model's reference ids, then it's better for you to use a has_many :through relationship.

   • In this case your UserGroup (mind the singularity) table will hold the additional data you need in the relationship. Then you declare as follows:

   • User model will have declared: has_many :groups, through: :user_groups

   • Group model will have declared: has_many :users, through: :user_groups
   • UserGroup model: belongs_to :user and belongs_to :group. Be careful not to index any of those as unique, database or application level.

For more details check http://guides.rubyonrails.org/association_basics.html#the-has-and-belongs-to-many-association

Hope you get your results!

Answer 2

Your solution (without causing an N+1) is activerecord-import

Answer 3

You can't mass-assign the association using update_all. update_all does a single UPDATE query but for association you actually need an INSERT query to insert the associated models. You could use a gem like bulk_insert to insert all the join objects with one INSERT query https://github.com/jamis/bulk_insert