v_applications.go

func (v V_APPLICATIONS) View(dialect string) sq.Query {
    USERS := sq.New[USERS]("u")
    USER_ROLES := sq.New[USER_ROLES]("ur")
    USER_ROLES_APPLICANTS := sq.New[USER_ROLES_APPLICANTS]("ura")
    PERIODS := sq.New[PERIODS]("p")
    FORMS := sq.New[FORMS]("f")
    APPLICATIONS := sq.New[APPLICATIONS]("a")

    applicants := NewCTE("applicants", nil, Postgres.
        Select(
            USERS.USER_ID,
            USER_ROLES.USER_ROLE_ID,
            USERS.DISPLAYNAME,
            USERS.EMAIL,
            USER_ROLES_APPLICANTS.APPLICATION_ID,
            USER_ROLES_APPLICANTS.APPLICANT_DATA,
        ).
        From(USERS).
        Join(USER_ROLES, USER_ROLES.USER_ID.Eq(USERS.USER_ID)).
        LeftJoin(USER_ROLES_APPLICANTS, USER_ROLES_APPLICANTS.USER_ROLE_ID.Eq(USER_ROLES.USER_ROLE_ID).
        Where(USER_ROLES.ROLE.EqString("applicant")),
    )

    application_questions := NewCTE("application_questions", nil, Postgres.
        Select(
            PERIODS.COHORT,
            PERIODS.MILESTONE,
            PERIODS.START_AT,
            PERIODS.END_AT,
            FORMS.QUESTIONS,
            FORMS.FORM_ID,
        ).
        From(PERIODS).
        Join(FORMS, FORMS.PERIOD_ID.Eq(PERIODS.PERIOD_ID)).
        Where(
            PERIODS.COHORT.NeString(""),
            PERIODS.STAGE.EqString("application"),
            PERIODS.MILESTONE.EqString(""),
            FORMS.NAME.EqString(""),
            FORMS.SUBSECTION.EqString("application"),
        ),
    )

    applicant_questions := NewCTE("applicant_questions", nil, Postgres.
        Select(
            PERIODS.COHORT,
            PERIODS.MILESTONE,
            PERIODS.START_AT,
            PERIODS.END_AT,
            FORMS.QUESTIONS,
            FORMS.FORM_ID,
        ).
        From(PERIODS).
        Join(FORMS, FORMS.PERIOD_ID.Eq(PERIODS.PERIOD_ID)).
        Where(
            PERIODS.COHORT.NeString(""),
            PERIODS.STAGE.EqString("application"),
            PERIODS.MILESTONE.EqString(""),
            FORMS.NAME.EqString(""),
            FORMS.SUBSECTION.EqString("applicant"),
        ),
    )

    applicant1 := applicants.As("applicant1")
    applicant2 := applicants.As("applicant2")

    return Postgres.
        From(APPLICATIONS).
        Join(application_questions, application_questions.Field("cohort").Eq(APPLICATIONS.COHORT)).
        Join(applicant_questions, applicant_questions.Field("cohort").Eq(APPLICATIONS.COHORT)).
        Join(applicant1, applicant1.Field("application_id").Eq(APPLICATIONS.APPLICATION_ID)).
        LeftJoin(applicant2, applicant2.Field("application_id").Eq(APPLICATIONS.APPLICATION_ID), applicant1.Field("user_id").Lt(applicant2.Field("user_id"))).
        OrderBy(
            APPLICATIONS.APPLICATION_ID.Asc(),
            applicant2.Field("user_id").Asc().NullsLast(),
        ).
        DistinctOn(APPLICATIONS.APPLICATION_ID).
        Select(
            // Application
            APPLICATIONS.APPLICATION_ID,
            APPLICATIONS.COHORT,
            APPLICATIONS.STATUS,
            APPLICATIONS.CREATOR_USER_ROLE_ID,
            APPLICATIONS.PROJECT_LEVEL,
            APPLICATIONS.MAGICSTRING,
            APPLICATIONS.SUBMITTED,

            // Applicant 1
            applicant1.Field("user_id").As("applicant1_user_id"),
            applicant1.Field("user_role_id").As("applicant1_user_role_id"),
            applicant1.Field("displayname").As("applicant1_displayname"),
            applicant1.Field("email").As("applicant1_email"),

            // Applicant 2
            applicant2.Field("user_id").As("applicant2_user_id"),
            applicant2.Field("user_role_id").As("applicant2_user_role_id"),
            applicant2.Field("displayname").As("applicant2_displayname"),
            applicant2.Field("email").As("applicant2_email"),

            // Questions and Answers
            application_questions.Field("form_id").As("application_form_id"),
            applicant_questions.Field("form_id").As("applicant_form_id"),
            application_questions.Field("questions").As("application_questions"),
            APPLICATIONS.APPLICATION_DATA.As("application_answers"),
            applicant_questions.Field("questions").As("applicant_questions"),
            applicant1.Field("applicant_data").As("applicant1_answers"),
            applicant2.Field("applicant_data").As("applicant2_answers"),

            APPLICATIONS.CREATED_AT,
            APPLICATIONS.UPDATED_AT,
            APPLICATIONS.DELETED_AT,
        )
}

v_applications.sql

WITH applicants AS (
    SELECT u.user_id, ur.user_role_id, u.displayname, u.email, ura.application_id, ura.applicant_data
    FROM users AS u JOIN user_roles AS ur USING (user_id) LEFT JOIN user_roles_applicants AS ura USING (user_role_id)
    WHERE ur.role = 'applicant'
)
,application_questions AS (
    SELECT p.cohort, p.milestone, p.start_at, p.end_at, f.questions, f.form_id
    FROM periods AS p JOIN forms AS f ON f.period_id = p.period_id
    WHERE p.cohort <> '' AND p.stage = 'application' AND p.milestone = '' AND f.name = '' AND f.subsection = 'application'
)
,applicant_questions AS (
    SELECT p.cohort, p.milestone, p.start_at, p.end_at, f.questions, f.form_id
    FROM periods AS p JOIN forms AS f ON f.period_id = p.period_id
    WHERE p.cohort <> '' AND p.stage = 'application' AND p.milestone = '' AND f.name = '' AND f.subsection = 'applicant'
)
SELECT DISTINCT ON (applications.application_id)
    -- Application
    applications.application_id
    ,applications.cohort
    ,applications.status
    ,applications.creator_user_role_id
    ,applications.project_level
    ,applications.magicstring
    ,applications.submitted

    -- Applicant 1
    ,applicant1.user_id AS applicant1_user_id
    ,applicant1.user_role_id AS applicant1_user_role_id
    ,applicant1.displayname AS applicant1_displayname
    ,applicant1.email AS applicant1_email

    -- Applicant 2
    ,applicant2.user_id AS applicant2_user_id
    ,applicant2.user_role_id AS applicant2_user_role_id
    ,applicant2.displayname AS applicant2_displayname
    ,applicant2.email AS applicant2_email

    -- Questions and Answers
    ,application_questions.form_id AS application_form_id
    ,applicant_questions.form_id AS applicant_form_id
    ,application_questions.questions AS application_questions
    ,applications.application_data AS application_answers
    ,applicant_questions.questions AS applicant_questions
    ,applicant1.applicant_data AS applicant1_answers
    ,applicant2.applicant_data AS applicant2_answers

    ,applications.created_at
    ,applications.updated_at
    ,applications.deleted_at
FROM
    applications
    JOIN application_questions ON application_questions.cohort = applications.cohort
    JOIN applicant_questions ON applicant_questions.cohort = applications.cohort
    JOIN applicants AS applicant1 ON applicant1.application_id = applications.application_id
    LEFT JOIN applicants AS applicant2 ON applicant2.application_id = applications.application_id AND applicant1.user_id < applicant2.user_id
ORDER BY
    applications.application_id ASC
    ,applicant2.user_id ASC NULLS LAST
;