Employees Counting

serv.rb

<<-SQL
 SELECT * FROM departments;
 id |   name    | parent_id |         created_at         |         updated_at
----+-----------+-----------+----------------------------+----------------------------
  1 | head1     |           | 2021-04-30 17:44:33.98764  | 2021-04-30 17:44:33.98764
  2 | head2     |           | 2021-04-30 17:44:37.132593 | 2021-04-30 17:44:37.132593
  3 | head3     |           | 2021-04-30 17:44:40.582593 | 2021-04-30 17:44:40.582593
  4 | child_1_1 |         1 | 2021-04-30 17:45:19.021174 | 2021-04-30 17:45:19.021174
  5 | child_1_2 |         1 | 2021-04-30 17:45:23.263489 | 2021-04-30 17:45:23.263489
  6 | child_2_1 |         2 | 2021-04-30 17:45:34.402745 | 2021-04-30 17:45:34.402745
  7 | child_2_2 |         2 | 2021-04-30 17:45:40.51696  | 2021-04-30 17:45:40.51696
  8 | child_2_3 |         2 | 2021-04-30 17:45:47.596611 | 2021-04-30 17:45:47.596611
(8 rows)
SQL

<<-SQL
 SELECT * FROM employees;
 id | department_id |         created_at         |         updated_at
----+---------------+----------------------------+----------------------------
  1 |             1 | 2021-04-30 17:46:31.958754 | 2021-04-30 17:46:31.958754
  2 |             2 | 2021-04-30 17:47:27.35611  | 2021-04-30 17:47:27.35611
  3 |             3 | 2021-04-30 17:47:29.845868 | 2021-04-30 17:47:29.845868
  4 |             4 | 2021-04-30 17:47:32.841422 | 2021-04-30 17:47:32.841422
  5 |             5 | 2021-04-30 17:47:35.395231 | 2021-04-30 17:47:35.395231
  6 |             6 | 2021-04-30 17:47:37.613046 | 2021-04-30 17:47:37.613046
  7 |             7 | 2021-04-30 17:47:40.32851  | 2021-04-30 17:47:40.32851
  8 |             8 | 2021-04-30 17:47:42.760543 | 2021-04-30 17:47:42.760543
  9 |             8 | 2021-04-30 19:40:12.251749 | 2021-04-30 19:40:12.251749
(9 rows)
SQL

 req = ActiveRecord::Base.connection.execute(<<-SQL
    SELECT departments.*,
    COUNT(DISTINCT employees.id) as employees_count,
    COUNT(DISTINCT children_departments.id) as children_departments_count,
    COUNT(DISTINCT nested_employees.id) as children_employees_count,
    COUNT(DISTINCT employees.id) + COUNT(nested_employees.id) total_employees_count
      FROM departments
        LEFT JOIN employees
          ON employees.department_id = departments.id
        LEFT JOIN departments children_departments
          ON children_departments.parent_id = departments.id
        LEFT JOIN employees nested_employees
          ON nested_employees.department_id = children_departments.id
    GROUP BY departments.id
  SQL
)
res = req.to_a


<<-JSON
[{"id"=>1,
  "name"=>"head1",
  "parent_id"=>nil,
  "created_at"=>"2021-04-30 17:44:33.98764",
  "updated_at"=>"2021-04-30 17:44:33.98764",
  "employees_count"=>1,
  "children_departments_count"=>2,
  "children_employees_count"=>2,
  "total_employees_count"=>3},
 {"id"=>2,
  "name"=>"head2",
  "parent_id"=>nil,
  "created_at"=>"2021-04-30 17:44:37.132593",
  "updated_at"=>"2021-04-30 17:44:37.132593",
  "employees_count"=>1,
  "children_departments_count"=>3,
  "children_employees_count"=>4,
  "total_employees_count"=>5},
 {"id"=>3,
  "name"=>"head3",
  "parent_id"=>nil,
  "created_at"=>"2021-04-30 17:44:40.582593",
  "updated_at"=>"2021-04-30 17:44:40.582593",
  "employees_count"=>1,
  "children_departments_count"=>0,
  "children_employees_count"=>0,
  "total_employees_count"=>1},
 {"id"=>4,
  "name"=>"child_1_1",
  "parent_id"=>1,
  "created_at"=>"2021-04-30 17:45:19.021174",
  "updated_at"=>"2021-04-30 17:45:19.021174",
  "employees_count"=>1,
  "children_departments_count"=>0,
  "children_employees_count"=>0,
  "total_employees_count"=>1},
 {"id"=>5,
  "name"=>"child_1_2",
  "parent_id"=>1,
  "created_at"=>"2021-04-30 17:45:23.263489",
  "updated_at"=>"2021-04-30 17:45:23.263489",
  "employees_count"=>1,
  "children_departments_count"=>0,
  "children_employees_count"=>0,
  "total_employees_count"=>1},
 {"id"=>6,
  "name"=>"child_2_1",
  "parent_id"=>2,
  "created_at"=>"2021-04-30 17:45:34.402745",
  "updated_at"=>"2021-04-30 17:45:34.402745",
  "employees_count"=>1,
  "children_departments_count"=>0,
  "children_employees_count"=>0,
  "total_employees_count"=>1},
 {"id"=>7,
  "name"=>"child_2_2",
  "parent_id"=>2,
  "created_at"=>"2021-04-30 17:45:40.51696",
  "updated_at"=>"2021-04-30 17:45:40.51696",
  "employees_count"=>1,
  "children_departments_count"=>0,
  "children_employees_count"=>0,
  "total_employees_count"=>1},
 {"id"=>8,
  "name"=>"child_2_3",
  "parent_id"=>2,
  "created_at"=>"2021-04-30 17:45:47.596611",
  "updated_at"=>"2021-04-30 17:45:47.596611",
  "employees_count"=>2,
  "children_departments_count"=>0,
  "children_employees_count"=>0,
  "total_employees_count"=>2}]
JSON