How to display Parent record with total number of its Child record in rails

Question

class Attachment < ActiveRecord::Base
  belongs_to :user, foreign_key: :creator_id
  belongs_to :deal_task, foreign_key: :relation_id
end
class DealTask < ActiveRecord::Base
   has_many :attachments, foreign_key: :relation_id
end

I have parent table called DealTask and child table called Attachment

I want a list of DealTask records with associated total number of attachments

Answer 1

I found the best solution for Parent child relationship count

counter_cache: true

because all above queries take too much time to load from database

so you all must prefer to use this

1-> Add one column in Parent table called DealTask

rails g migration AddAttachmentsCountToDealTask attachments_count:integer

2-> Open Migration add Edit it

class AddAttachmentCountToDealTask < ActiveRecord::Migration[5.0]

 def up
  add_column :deal_tasks, :attachments_count, :integer, default: 0
  DealTask.reset_column_information
  DealTask.find_each do |deal_task|
    DealTask.reset_counters deal_task.id, :attachments
  end
 end
 def down
  remove_column :deal_tasks, attachments_count
 end
end

So when you rollback the migration it will not raise an error or exception

you can also use any loop instead of using

find_each, DealTask.all.each do...end

but yes, While resetting counter Must use class name like

DealTask.reset_counters

3-> Set Counter cache

class Attachment < ActiveRecord::Base
 belongs_to :deal_task, foreign_key: :relation_id, counter_cache: true
end
class DealTask < ActiveRecord::Base
 has_many :attachments, foreign_key: :relation_id
end

suppose name of your model is sub_tasks than your counter_cache column must be

sub_tasks_count

if you want your own column name than you have to specify that column name in counter_cache

suppose column name is total_subtasks than

belongs_to :deal_task, foreign_key: :relation_id, counter_cache: :total_subtasks

and make changes accordingly for updating counter_cache

now when you Add any Attachment, attachments_count column increase by 1 and this is done automatically by **counter_cache

one Problem is there ** when you delete any child counter_cache is unable to decrease **

so for that solution make a callback

class Attachment < ActiveRecord::Base
 belongs_to :deal_task, foreign_key: :relation_id, counter_cache: true
 before_destroy :reset_counter
 private
 def reset_counter
  DealTask.reset_counters(self.relation.id, :attachments)
 end
end

so when you delete any attachments it will reset countet_cache for its Parent by relation_id which is parent_id or Foreign_key for attachments

for more info see video on Railscast counter cache 23

Answer 2

Try this

DealTask.all.map { |deal_task| deal_task.attachments.ids }.count

Answer 3

DealTask.first.attachments.count #This will give count of attachemenets

#To show all records and all the count

DealTask.find_each do |dt|
      print dt.inspect
      print "\n"
      print dt.attachments.count
end

Or

DealTask.joins(:attachments).select("deal_tasks.*, count(attachements.id) as count").group("deal_tasks.id")

For much nicer format

DealTask.joins(:attachments)
        .select("deal_tasks.id, deal_tasks.name, count(attachements.id) as attachments")
        .group("deal_tasks.id")
        .collect(&:attributes)
#This will gve you something like

[
{"id"=>34332630, "name"=>"some name", "attachments"=>1}, 
{"id"=>71649461, "name"=>"some name", "attachments"=>1}
]

This will be lot faster as you get all data in a single query

Answer 4

DealTask.all.map do |deal_task|
  deal_task.
    attributes.
    with_indifferent_access.
    slice(:id, :name).
    merge!(total_attachment: deal_task.attachments.count)
end

Or, if you don't care about indifferent access and you don't mind having all the DealTask attributes, you can write this with on a single line:

DealTask.all.map{|deal_task| deal_task.attributes.merge!(total_attachments: deal_task.attachments.count)}

Breaking it down...

DealTask.all.map do |deal_task|
  ...
end

Is going to return an array. The array will contain the results of the do block.

deal_task.
  attributes.
  with_indifferent_access

Gives you the attributes of each deal_task in a hash that can be access with strings or symbols (thus, "indifferent_access").

deal_task.
  attributes.
  with_indifferent_access.
  slice(:id, :name)

Keeps only the :id and :name of the deal_task hash.

merge!(total_attachments: deal_task.attachments.count)

Adds the attachments count to your hash with the key total_attachments.

Results should look something like:

[
  {id: 1, name: 'name1', total_attachments: 12},
  {id: 2, name: 'name2', total_attachments: 3}
]