Skip to content

Instantly share code, notes, and snippets.

@bokwoon95

bokwoon95/v_applications.go

Last active June 29, 2021 03:25
Show Gist options
  • Save bokwoon95/9164af4ce27b09325b320563c75579e6 to your computer and use it in GitHub Desktop.
Save bokwoon95/9164af4ce27b09325b320563c75579e6 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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,
)
}
Raw
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
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment