user_workspaces.sql

-- user_workspaces_v01
-- Pull Organizations and Workspaces affiliated with, 
-- and also organizations we have projects we are collaborating on 

-- first version :: ugly and super slow, see updated version next

  WITH user_workspaces AS (
        SELECT a.user_id, 
               o.name
          FROM user_affiliations a
    INNER JOIN organizations o 
            ON a.affiliate_id = o.id AND a.affiliate_type = 'Organization'

         UNION 

        SELECT a.user_id,
               w.name
          FROM user_affiliations a 
    INNER JOIN workspaces w
            ON a.affiliate_id = w.id AND a.affiliate_type = 'Workspace'

         UNION

        SELECT a.user_id,
               o.name
          FROM user_affiliations a
    INNER JOIN projects p
            ON a.affiliate_id = p.id AND a.affiliate_type = 'Project'
    INNER JOIN organizations o
            ON e.organization_id = o.id
  )

        SELECT DISTINCT ON (user_id, name)
               user_id,
               name
          FROM user_workspaces
      ORDER BY name

user_workspaces_v02.sql

-- Rework to be 60x more performant when 10,000 organizations and 10,000 user_affiliations.
-- 

      SELECT DISTINCT ON(user_id, name)
             user_id,
             CASE affiliate_type
             WHEN 'Project'       THEN (SELECT o.name FROM projects p
                                                INNER JOIN organizations o
                                                        ON p.organization_id = o.id)
             WHEN 'Organization'  THEN (SELECT   name FROM organizations
                                                     WHERE id = affiliate_id)
             WHEN 'Workspace'     THEN (SELECT   name FROM workspaces
                                                     WHERE id = affiliate_id)
             END as name
        FROM user_affiliations
    ORDER BY name