select-folders.sql

WITH RECURSIVE folder_tree(id, depth) AS (
  SELECT 
    fo.id, 
    1
  FROM 
    folder AS fo
    WHERE (
      (${ids}::bigint[] IS NULL AND fo.parent_folder_id IS NULL) 
      OR fo.id = ANY (${ids}::bigint[])
    ) AND fo.user_group_id = ${userGroupId}
  UNION ALL
  SELECT 
    fo.id, 
    ft.depth + 1
  FROM 
    folder AS fo, 
    folder_tree AS ft
  WHERE fo.parent_folder_id = ft.id
)
SELECT 
  fo.id, 
  ft.depth,
  fo.user_group_id,
  fo.user_id,
  fo.name, 
  fo.parent_folder_id,
  fo.active,
  fo.insertion_time
FROM 
  folder_tree AS ft, 
  folder AS fo
WHERE
  ft.id = fo.id
ORDER BY ft.depth, fo.name;