projects sql testing

projects.sql

CREATE TABLE projects (
  id serial PRIMARY KEY,
  type text,
  proj_name text,
  proj_desc text,
  proj_loc text,
  proj_start date,
  proj_duration integer,
  attributes jsonb,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now()
);

CREATE TABLE project_stages (
  id serial PRIMARY KEY,
  proj_id integer REFERENCES projects(id),
  stage_id integer REFERENCES projects(id)
);

CREATE TABLE users (
  id serial PRIMARY KEY,
  name text
);

CREATE TABLE project_users (
  id serial PRIMARY KEY,
  type text,
  user_id integer REFERENCES users(id),
  project_id integer REFERENCES projects(id)
);

CREATE TABLE species (
  id serial PRIMARY KEY,
  species text
);

CREATE TABLE project_species (
  id serial PRIMARY KEY,
  species_id integer REFERENCES species(id),
  project_id integer REFERENCES projects(id)
);

SELECT 
  projects.proj_name,
  projects.proj_desc,
  projects.type,
  project_stages.proj_id,
  project_stages.stage_id
FROM projects
  LEFT JOIN project_stages ON projects.id = project_stages.proj_id
WHERE projects.type = 'project';

SELECT 
  projects.proj_name,
  projects.proj_desc,
  projects.type,
  project_stages.proj_id,
  project_stages.stage_id
FROM projects
  LEFT JOIN project_stages ON projects.id = project_stages.stage_id
WHERE projects.type = 'stage';

-- PROJECTS AND SUB PROJECTS
WITH p AS (
  SELECT 
    projects.proj_name,
    projects.proj_desc,
    projects.type,
    project_stages.proj_id,
    project_stages.stage_id
  FROM projects
    LEFT JOIN project_stages ON projects.id = project_stages.proj_id
  WHERE projects.type = 'project' 
),

s AS (
  SELECT 
    projects.proj_name,
    projects.proj_desc,
    projects.type,
    project_stages.proj_id,
    project_stages.stage_id
  FROM projects
    LEFT JOIN project_stages ON projects.id = project_stages.stage_id
  WHERE projects.type = 'stage'
)

SELECT p.*, s.*
FROM p
  LEFT JOIN s ON p.stage_id = s.stage_id;

-- JSON serialization
WITH stages AS (
  SELECT 
    projects.proj_name,
    projects.proj_desc,
    projects.type,
    project_stages.proj_id,
    project_stages.stage_id
  FROM projects
    LEFT JOIN project_stages ON projects.id = project_stages.stage_id
  WHERE projects.type = 'stage'
)

SELECT 
  id,
  proj_name,
  proj_desc,
  (
    SELECT to_jsonb(array_agg(to_jsonb(s)))
    FROM (
      SELECT * 
      FROM stages
      WHERE proj_id = projects.id
    ) s
  ) AS Stages
FROM projects
WHERE projects.type = 'project'
ORDER BY projects.id;

-- PROJECT AND ALL SPECIES
SELECT 
  projects.id,
  projects.proj_name,
  species.species
FROM projects
  INNER JOIN project_species ON projects.id = project_species.project_id
  INNER JOIN species ON project_species.species_id = species.id;

-- PROJECTS AND OWNERS
SELECT 
  projects.id,
  projects.proj_name,
  project_users.type,
  users.name
FROM projects
  INNER JOIN project_users ON projects.id = project_users.project_id
  INNER JOIN users ON project_users.user_id = users.id;

-- -- FURTHER BREAKING DOWN ABOVE INTO LEADS AND COLABS
SELECT 
  projects.id,
  projects.proj_name,
  project_users.type,
  users.name
FROM projects
  INNER JOIN project_users ON projects.id = project_users.project_id
  INNER JOIN users ON project_users.user_id = users.id
WHERE project_users.type = 'lead';

SELECT 
  projects.id,
  projects.proj_name,
  project_users.type,
  users.name
FROM projects
  INNER JOIN project_users ON projects.id = project_users.project_id
  INNER JOIN users ON project_users.user_id = users.id
WHERE project_users.type = 'colab';

-- FULL JSON SERIALIZATION
COPY (

WITH stages AS (
  SELECT 
    projects.proj_name,
    projects.proj_desc,
    projects.type,
    project_stages.proj_id,
    project_stages.stage_id
  FROM projects
    LEFT JOIN project_stages ON projects.id = project_stages.stage_id
  WHERE projects.type = 'stage'
),

species AS (
  SELECT 
    projects.id,
    projects.proj_name,
    species.species
  FROM projects
    INNER JOIN project_species ON projects.id = project_species.project_id
    INNER JOIN species ON project_species.species_id = species.id 
),

leads AS (
  SELECT 
    projects.id,
    projects.proj_name,
    project_users.type,
    users.name
  FROM projects
    INNER JOIN project_users ON projects.id = project_users.project_id
    INNER JOIN users ON project_users.user_id = users.id
  WHERE project_users.type = 'lead'
),

colabs AS (
  SELECT 
    projects.id,
    projects.proj_name,
    project_users.type,
    users.name
  FROM projects
    INNER JOIN project_users ON projects.id = project_users.project_id
    INNER JOIN users ON project_users.user_id = users.id
  WHERE project_users.type = 'colab'
),

srl AS (
  SELECT 
    id,
    proj_name,
    proj_desc,
    
    (
      SELECT to_jsonb(array_agg(to_jsonb(s)))
      FROM (
        SELECT * 
        FROM stages
        WHERE proj_id = projects.id
      ) s
    ) AS Stages,

    (
      SELECT to_jsonb(array_agg(to_jsonb(species)))
      FROM species
      WHERE id = projects.id
    ) AS Species,

    (
      SELECT to_jsonb(array_agg(to_jsonb(leads)))
      FROM leads
      WHERE id = projects.id
    ) AS Leads,

    (
      SELECT to_jsonb(array_agg(to_jsonb(colabs)))
      FROM colabs
      WHERE id = projects.id
    ) AS Colabs
    
  FROM projects
  WHERE projects.type = 'project'
  ORDER BY projects.id
)

SELECT to_jsonb(array_agg(to_jsonb(srl)))
FROM srl

) to '/Users/mitchellgritts/Documents/NDOW/enc-db-schema/json-out.json';