dashboard_data_per_root_category.sql

with recursive category_tree AS (
  select category_id, "name", ARRAY[]::UUID[] AS ancestors
  from categories WHERE parent_id IS NULL
  union all
  select categories.category_id, categories."name", category_tree.ancestors || categories.parent_id
  from categories, category_tree
  where categories.parent_id = category_tree.category_id
), document_count_per_sub_category as (
  select 
    d.document_name as "name",
    d.url_id as document_id,
    d.viewed,
    d.downloaded,
    (
      select d.document_name as searchable_document
    )    
  FROM category_tree ct
  left join category_document_access cd
  on cd.category_id = ct.category_id
  left join (
    select 
      d_inner.id, 
      d_inner.document_name,
      d_inner.url_id,
      count (udv.*) as viewed,
      count (udd.*) as downloaded
    from documents d_inner
    left join user_document_view udv
    on d_inner.id = udv.document_id
    left join user_document_download udd
    on d_inner.id = udd.document_id
    where d_inner.active
    group by d_inner.id
  ) d
  on cd.document_id = d.id
  WHERE (select category_id from categories c where url_id = $1) = any(ct.ancestors)
  and d.id is not null
  group by ct."name", ct.category_id, d.viewed, d.downloaded, d.document_name, d.url_id
  ), searched_table as (
    select * from document_count_per_sub_category
    where to_tsvector(searchable_document) @@ to_tsquery('<SEARCH QUERY>:*')
  )
 select "name", viewed, downloaded from searched_table;