example.sql

-- What I have
-- >>> from django.db.models.expressions import *
-- >>> from foo.utils import GroupConcat 
-- GroupConcat is essentially just a string aggregator... code is here if you're curious https://dpaste.org/Twpu/slim#L15,20
-- >>> window = {'partition_by': [F('id'),], 'order_by': [F('workload__submission__employee__role__level').desc(),], 'frame': RowRange()}
-- >>> Project.objects.annotate(team=Window(expression=GroupConcat('workload__submission__employee__goes_by'), **window))

SELECT
    "staffing_project"."id",
    "staffing_project"."name",
    "staffing_project"."description",
    "staffing_project"."status",
    "staffing_project"."client_id",
    GROUP_CONCAT("staffing_employee"."goes_by") OVER (
        PARTITION BY "staffing_project"."id"
        ORDER BY "staffing_role"."level" ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS "team"
FROM
    "staffing_project"
LEFT OUTER JOIN "staffing_workload" ON ("staffing_project"."id" = "staffing_workload"."project_id")
LEFT OUTER JOIN "staffing_submission" ON ("staffing_workload"."submission_id" = "staffing_submission"."id")
LEFT OUTER JOIN "staffing_employee" ON ("staffing_submission"."employee_id" = "staffing_employee"."id")
LEFT OUTER JOIN "staffing_role" ON ("staffing_employee"."role_id" = "staffing_role"."id");

---------------------------------------------------------------------------------------------------------------------------------------
-- what I wish I had
SELECT
    "staffing_subquery"."id",
    "staffing_subquery"."name",
    "staffing_subquery"."description",
    "staffing_subquery"."status",
    "staffing_subquery"."client_id",
    GROUP_CONCAT("staffing_subquery"."goes_by") OVER (
        PARTITION BY "staffing_subquery"."id"
        ORDER BY "staffing_subquery"."level" DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS "team"
FROM (
    -----------------------------------------------
    -- want to add this 'DISTINCT' to a subquery --
    SELECT DISTINCT
    -----------------------------------------------
       "staffing_project"."id",
       "staffing_project"."name",
       "staffing_project"."description",
       "staffing_project"."status",
       "staffing_project"."client_id",
       "staffing_employee"."goes_by",
       "staffing_role"."level"
    FROM "staffing_project"
    LEFT OUTER JOIN "staffing_workload" ON ("staffing_project"."id" = "staffing_workload"."project_id")
    LEFT OUTER JOIN "staffing_submission" ON ("staffing_workload"."submission_id" = "staffing_submission"."id")
    LEFT OUTER JOIN "staffing_employee" ON ("staffing_submission"."employee_id" = "staffing_employee"."id")
    LEFT OUTER JOIN "staffing_role" ON ("staffing_employee"."role_id" = "staffing_role"."id")
) staffing_subquery;