Implement a SQL view to make it easier to query files in a nested folder

[simonw]

Working with nested data in SQL is tricky, can I make it easier with a view or canned query?

[simonw]

Here's a query that returns all notes in folder 1, including notes in descendant folders:

with recursive nested_folders(folder_id, descendant_folder_id) as (
  -- base case: select all immediate children of the root folder
  select id, id from folders where parent is null
  union all
  -- recursive case: select all children of the previous level of nested folders
  select nf.folder_id, f.id from nested_folders nf
  join folders f on nf.descendant_folder_id = f.parent
)
-- Find notes within all descendants of folder 1
select *
from notes
where folder in (
  select descendant_folder_id from nested_folders where folder_id = 1
);

With assistance from ChatGPT. Prompts were:

SQLite schema:

CREATE TABLE [folders] (
   [id] INTEGER PRIMARY KEY,
   [long_id] TEXT,
   [name] TEXT,
   [parent] INTEGER,
   FOREIGN KEY([parent]) REFERENCES [folders]([id])
);

Write a recursive CTE that returns the following:

folder_id | descendant_folder_id

With a row for every nested child of every folder - so the top level folder has lots of rows

Then I tweaked it a bit, then ran this:

WITH RECURSIVE nested_folders(folder_id, descendant_folder_id) AS (
  -- base case: select all immediate children of the root folder
  SELECT id, id FROM folders WHERE parent IS NULL
  UNION ALL
  -- recursive case: select all children of the previous level of nested folders
  SELECT nf.folder_id, f.id FROM nested_folders nf
  JOIN folders f ON nf.descendant_folder_id = f.parent
)
-- select all rows from the recursive CTE
SELECT * from notes where folder in (select descendant_folder_id FROM nested_folders where folder_id = 1)

Convert all SQL keywords to lower case, and re-indent

[simonw]

Here's what the CTE from that looks like:

[simonw]

I improved the readability by removing some unnecessary table aliases:

with recursive nested_folders(folder_id, descendant_folder_id) as (
  -- base case: select all immediate children of the root folder
  select id, id from folders where parent is null
  union all
  -- recursive case: select all children of the previous level of nested folders
  select nested_folders.folder_id, folders.id from nested_folders
  join folders on nested_folders.descendant_folder_id = folders.parent
)
-- Find notes within all descendants of folder 1
select *
from notes
where folder in (
  select descendant_folder_id from nested_folders where folder_id = 1
);