Skip to content

Instantly share code, notes, and snippets.

@nthj
Last active February 21, 2016 14:48
Show Gist options
  • Save nthj/8b4282724f9bd9ba4187 to your computer and use it in GitHub Desktop.
Save nthj/8b4282724f9bd9ba4187 to your computer and use it in GitHub Desktop.
-- 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
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment