Figure out a good recursive CTE for showing the folder hierarchy

[simonw]

Maybe even turn this into a SQL view

[simonw]

Started one here but I don't think it's right yet: https://github.com/simonw/google-drive-to-sqlite/issues/9#issuecomment-1045800441

with recursive folders as (
    select id, name, _parent
        from drive_folders
        where _parent is null
    union
        select id, name, _parent || '/' || drive_folders._parent
        from drive_folders
  )
select * from folders

[simonw]

This is a great tutorial: https://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial/

Figured this out to return 1 to 10:

with recursive counter(i) as (
  select
    1
  union all
  select
    i + 1
  from
    counter
)
select
  *
from
  counter
limit
  10

Adapted this from the tutorial to use a values clause to populate company rather than creating a physical table for it. Note that I had to put with recursive at the very start even though the company CTE isn't recursive, but the approvers one is.

with recursive company(name, approver) as (
  values
    ('David', NULL),
    ('Matt', 'David'),
    ('Jason', 'David'),
    ('Ryan', 'David'),
    ('Mike', 'Matt'),
    ('Carlos', 'Matt'),
    ('Garrett', 'Jason'),
    ('Puneet', 'Jason'),
    ('Joanie', 'Ryan')
),
approvers(x) as (
  select
    'Joanie'
  union all
  select
    company.approver
  from
    company,
    approvers
  where
    company.name = approvers.x
    and company.approver is not null
)
select
  *
from
  approvers

[simonw]

OK, this one is working pretty well:

with recursive folders as (
  select
    '' as indent,
    name,
    name as path,
    _parent,
    id
  from
    drive_folders
  where
    _parent is null
  union all
  select
    indent || '-',
    drive_folders.name, 
    path || ' / ' || drive_folders.name,
    drive_folders._parent,
    drive_folders.id
  from
    drive_folders join folders on drive_folders._parent = folders.id
)
select
  indent || ' ' || name, path
from
  folders
order by path

Output looks like this:

Case Files | Case Files
- Alameda | Case Files / Alameda 
-- Alameda County District Attorney | Case Files / Alameda County / Alameda County District Attorney