A Postgres view that denormalizes to a Scala representation of a Google Drive folder

gfolder_view.sql

CREATE VIEW gfolder_view AS
  SELECT
    f.id, f.e_tag, f.url, f.icon_url, f.title, m.name, f.file_owner,
    p.parent_str, c.child_str, s.scion_str, f.created, f.modified
  FROM
    gfiles AS f
      JOIN mimes AS m ON (f.mime_type = m.name)
      LEFT JOIN (SELECT DISTINCT id, string_agg(parent, ',' ORDER BY parent) AS parent_str
                 FROM parents GROUP BY id) AS p ON (f.id = p.id)
      LEFT JOIN (SELECT DISTINCT id, string_agg(child, ',' ORDER BY child) AS child_str
                 FROM children GROUP BY id) AS c ON (f.id = c.id)
      LEFT JOIN (SELECT DISTINCT id, string_agg(scion, ',' ORDER BY scion) AS scion_str
                 FROM scion_view GROUP BY id) AS s ON (f.id = s.id)
  WHERE
    m.category = 'folder';