Query model data with relationship count and performance-CodePudding

I have problems with query performance with Postgresql and Rails counting related models while retrieving data.

class MasterModel
  # few fields, like name, description and such

  has_and_belongs_to_many :business_models, class: 'BusinessModel'
end

class BusinessModel
  # Lots of important information, many fields

  has_and_belongs_to_many :master_models, class: 'MasterModel'
end

The use case in question being that business_model can be related to any amount of master_model so typically you should have a small amount of master_model, a great amount of business_model and a even bigger amount of many to many relationships.

When showing master_model index page, you can visualize its information and a delete button only enabled when there are no relationships, hence the reason why its important to count the relationship in its representation.

So I tried some ways to achieve this:

Includes relationship is incredibly slow in ActiveRecord but not in query time. At least it has no N 1.

MasterModel.includes(:business_models).limit(50).offset(0).each do |master|
  master.business_models.size
end

No includes. We have N 1 but is incredibly fast as long as model pagination is reasonable.

MasterModel.limit(50).offset(0).each do |master|
  master.business_models.size
end

Given that I only need to know if relationships exists or not I tried a select with exists. Single query and fast.

MasterModel.select(
  :id,
  :name,
  :description.
  'NOT EXISTS (
    SELECT many.master_id
    FROM many
    WHERE many.master_id = master.id
  ) AS removable'
).limit(50).offset(0).each do |master|
  master.business_models.removable
end

In the end, I chose the 3rd choice but I am not totally convinced. What would be the Rails way? Am I doing something wrong in the other cases?

CodePudding user response：

If you would used has_many through association you would be able to use counter_cache but HABTM doesn't support counter_cache so that you can implement your own counter_cache

First of all you need to add new integer column to the master_models table called business_models_count

add_column :master_models, :business_models_count, :integer, default: 0

And add next code to your model MasterModel

class MasterModel
  has_and_belongs_to_many :business_models, class: 'BusinessModel', before_add: :inc_business_models_count, before_remove: :dec_business_models_count

  private

  def inc_business_models_count(*)
    self.increment!(:business_models_count)
  end

  def dec_business_models_count(*)
    self.decrement!(:business_models_count)
  end
end

And write some rake task which goes through MasterModel records and update counter for existing records.

It can be done like this:

MasterModel.find_each do |master|
  master.increment!(:business_models_count, master.business_models.size)
end

And after that you will be able to get business_models_count of each MasterModel instance without N 1

MasterModel.limit(50).offset(0).each do |master|
  master.business_models_count
end