CODEWITHSUNDEEP
sqlruby-on-rails
Rigi
Rigi

Reputation: 3490

Getting rid of N+1 query when destroying associated objects

I am creating a Blog app, I am trying to write the destroy method in Users Controller to handle the situation, when User is being destroyed. I want it to trigger the action of deleting all related objects, but when I specify" dependent: :destroy" in the models I get N+1 query (or N*M if that's possible..) - every single article is being deleted one by one, then all the comments, all the tags, all the taggings (table to handle HABTM relationship).

I have tried to write the Service Object to get rid of this long query, but I can't get it to work, because I "violate foreign key constraint". What are the possibilites here? I don't want to allow nils in my DB just to be able to easily clean the DB by running query that looks for nils in DB (to avoid strange input by users), so dependent: :nullify won't work here... Also I'd rather not use Callbacks if that's possible.

What also bothers me when choosing the solution with Services, that I first wait for User to be deleted and then call Service to clean Orhpans.. but when the code is executed wrongly, I end up with nils - should I use transactions on this?

User can be the author of article, which is tagged, it can be commented by other users.

Here are the main classes:

User:

class User < ApplicationRecord
  devise :database_authenticatable, :registerable,
        :recoverable, :rememberable, :trackable, :validatable

  has_many :articles, foreign_key: 'author_id'
  has_many :comments, foreign_key: 'author_id'
  validates :email, presence: true
end

Article:

class Article < ApplicationRecord
  include ActiveModel::Validations
  belongs_to :author, class_name: 'User'
  has_many :taggings
  has_many :tags, through: :taggings
  has_many :comments
end

Comment:

class Comment < ApplicationRecord
  belongs_to :author, class_name: 'User'
  belongs_to :article
  belongs_to :parent, class_name: 'Comment', optional: true
  has_many :children, class_name: 'Comment', foreign_key: 'parent_id', dependent: :delete_all
  acts_as_tree order: 'created_at ASC'
end

Tag:

class Tag < ApplicationRecord
  has_many :taggings
  has_many :articles, through: :taggings
end

Tagging:

class Tagging < ApplicationRecord
  belongs_to :tag
  belongs_to :article
end

Upvotes: 0

Views: 35

Answers (1)

Daniel Westendorf
Daniel Westendorf

Reputation: 3465

This is expected behavior. You have a few options:

1) You can create a service object that does all the deletion manually through queries, starting with your furthest-away relationship. for example:

user = User.find(id_of_user_to_destroy)

user.articles.each do |article|
  article = Article.find(id_of_article_to_destroy)
  Tag.where(articles: [article]).delete_all #delete_all skips callbacks
  Tagging.where(article: article).delete_all
  Comment.where(...).delete_all
  article.destroy
end

user.destroy

This is usually more performance, but harder to maintain.

2) Mark the user as "trashed" somehow (boolean field) and then call User.destroy in a background job. You'll still have the high number of db queries, however, it'll be easier to maintain with the native AR dependency attributes.

Upvotes: 2

Related Questions