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
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.
If you don't need to manage other data (relationship attributes) in the relationship table:
groups_users, in your case. Rails will look for habtm relationship table in alphabetic order.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_groupsUserGroup 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!
Your solution (without causing an N+1) is activerecord-import
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