I have comments, cases, case steps and tasks table with the relation as:
Comments model:
belongs_to :commentable, polymorphic: true, counter_cache: true
Cases model:
has_many :case_steps, class_name: 'CaseStep', foreign_key: 'case_id', dependent: :destroy
has_many :tasks, through: :case_steps
Case steps model:
belongs_to :case, class_name: 'Case', foreign_key: 'case_id'
has_many :tasks, class_name: 'Task', foreign_key: 'case_step_id', dependent: :destroy
Tasks model:
belongs_to :case_step, class_name: 'CaseStep', foreign_key: 'case_step_id'
All the three tables cases, case_steps and tasks have the field: comments_count
On my case index page I have to display the count of all the commnts_count belonging to a particular case. Means if a case 'Case1' has two case steps 'Case Step1' and 'Case Step2' and 'Case Step1' has three tasks 'Task1', 'Task2' and 'Task3', And 'Case Step2' has two tasks 'Task4' and 'Task5' , then it should display the count of all comments_count from all the tables 'Case1', 'Case Step1', 'Case Step2', 'Task1', 'Task2' and 'Task3', 'Task4' and 'Task5'.
Now getting how to write the exact query to fetch all the count... Tried a lot.
I will suggest you to write a find_by_sql query with the following sql query. Just replace 1 for your case id
Select id , case_name , count(comments_count) from cases where id =1 group by id, case_name
Union
Select case_type_id , case_type_name , count(comments_count) from case_types where case_id =1 group by case_id, case_type_name
Union
Select case_step_id , case_step_name , count(comments_count) from case_steps where case_step_id in (select id from case_steps where case_id =1) group by case_step_id , case_step_name