Optimize SQL per Mathias suggestion

ammario · ammario · commit b0eece835a82 · 2024-01-30T14:24:13.000-06:00
diff --git a/coderd/database/queries/workspacebuildparameters.sql b/coderd/database/queries/workspacebuildparameters.sql
@@ -16,33 +16,25 @@ WHERE
     workspace_build_id = $1;
 
 -- name: GetUserWorkspaceBuildParameters :many
-SELECT
-    sub.name,
-    sub.value
-FROM (
-    SELECT
-        wbp.name,
-        wbp.value,
-        wb.created_at,
-        ROW_NUMBER() OVER (PARTITION BY wbp.name ORDER BY wb.created_at DESC) as rn
-    FROM
-        workspace_build_parameters wbp
-    JOIN 
-        workspace_builds wb ON wb.id = wbp.workspace_build_id
-    JOIN
-        workspaces w ON w.id = wb.workspace_id
-    JOIN
-        template_version_parameters tvp ON tvp.template_version_id = wb.template_version_id
-    WHERE
-        w.owner_id = $1
-        AND wb.transition = 'start'
-        AND w.template_id = $2
-        AND tvp.ephemeral = false
-        AND tvp.name = wbp.name
-) sub
+-- name: GetUserWorkspaceBuildParameters :many
+SELECT DISTINCT ON (tvp.name)
+    tvp.name,
+    wbp.value
+FROM
+    workspace_build_parameters wbp
+JOIN
+    workspace_builds wb ON wb.id = wbp.workspace_build_id
+JOIN
+    workspaces w ON w.id = wb.workspace_id
+JOIN
+    template_version_parameters tvp ON tvp.template_version_id = wb.template_version_id
 WHERE
-    sub.rn = 1
-ORDER BY sub.created_at DESC
--- If there are many distinct parameters, 
--- we only want the most recent ones. 
+    w.owner_id = $1
+    AND wb.transition = 'start'
+    AND w.template_id = $2
+    AND tvp.ephemeral = false
+    AND tvp.name = wbp.name
+ORDER BY
+    tvp.name, wb.created_at DESC
 LIMIT 100;
+
diff --git a/coderd/users.go b/coderd/users.go
@@ -609,7 +609,7 @@ func (api *API) userAutofillParameters(rw http.ResponseWriter, r *http.Request)
 		return
 	}
 
-	var sdkParams []codersdk.UserParameter
+	sdkParams := []codersdk.UserParameter{}
 	for _, param := range params {
 		sdkParams = append(sdkParams, codersdk.UserParameter{
 			Name:  param.Name,
diff --git a/coderd/users_test.go b/coderd/users_test.go
@@ -1821,8 +1821,9 @@ func TestUserAutofillParameters(t *testing.T) {
 
 		// Verify that latest parameter value is returned.
 		dbfake.WorkspaceBuild(t, db, database.Workspace{
-			OwnerID:    u2.ID,
-			TemplateID: version.Template.ID,
+			OrganizationID: u1.OrganizationID,
+			OwnerID:        u2.ID,
+			TemplateID:     version.Template.ID,
 		}).Params(
 			database.WorkspaceBuildParameter{
 				Name:  "param",

Original file line number	Diff line number	Diff line change
`@@ -609,7 +609,7 @@ func (api API) userAutofillParameters(rw http.ResponseWriter, r http.Request)`
`609`	`609`	`return`
`610`	`610`	`}`
`611`	`611`
`612`		`- var sdkParams []codersdk.UserParameter`
	`612`	`+ sdkParams := []codersdk.UserParameter{}`
`613`	`613`	`for _, param := range params {`
`614`	`614`	`sdkParams = append(sdkParams, codersdk.UserParameter{`
`615`	`615`	`Name: param.Name,`