2

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

Ahmad hamza
  • 1,816
  • 1
  • 23
  • 46

3 Answers3

0

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!

GPrimola
  • 1,685
  • 1
  • 12
  • 8
  • user_group table has business logic because of which it has been added. As in user can belongs to many groups and groups can simultaneously have accesses based on their permissions. – Ahmad hamza Apr 27 '17 at 10:28
  • I can though add the `has_many through association`. The logicis that i need to `assign tasks to different user_groups` but the way it has been written previously is causing `n+1` queries. I need to solve that. – Ahmad hamza Apr 27 '17 at 10:33
  • I could see that, but the problem is that you don't have that `user_group_ids` column in your Task table. You can solve that writting a migration to add that column, however my feeling is that you have a modeling problem that is not visible yet - your relationships seems confusing. – GPrimola Apr 27 '17 at 13:46
  • The join table will automatically add it if using `update_attributes` as mentioned in the question. The query formation for `update_all` is wrong. – Ahmad hamza Apr 27 '17 at 16:23
  • @Ahmadhamza Did you figure this out? I am trying to do the exact same thing. I would expect Rails to be able to detect that I am trying to update association relationships the same way it would if I did task.user_ids = (ids), but it doesn't seem to work. I am looking for a work around as I don't want to get dirty splitting params and going through loops. – Tashows Jun 12 '19 at 16:52
0

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

Ohad Dahan
  • 371
  • 3
  • 14
0

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

arieljuod
  • 15,460
  • 2
  • 25
  • 36