add pinned status to GetWorkspaces/GetAuthorizedWorkspaces queries

johnstcn · johnstcn · commit 804f4a154639 · 2024-01-22T08:53:01.000Z
diff --git a/coderd/database/modelqueries.go b/coderd/database/modelqueries.go
@@ -213,9 +213,9 @@ func (q *sqlQuerier) GetAuthorizedWorkspaces(ctx context.Context, arg GetWorkspa
 	// The name comment is for metric tracking
 	query := fmt.Sprintf("-- name: GetAuthorizedWorkspaces :many\n%s", filtered)
 	rows, err := q.db.QueryContext(ctx, query,
+		arg.OwnerID,
 		arg.Deleted,
 		arg.Status,
-		arg.OwnerID,
 		arg.OwnerUsername,
 		arg.TemplateName,
 		pq.Array(arg.TemplateIDs),
@@ -253,6 +253,7 @@ func (q *sqlQuerier) GetAuthorizedWorkspaces(ctx context.Context, arg GetWorkspa
 			&i.TemplateName,
 			&i.TemplateVersionID,
 			&i.TemplateVersionName,
+			&i.Pinned,
 			&i.Count,
 		); err != nil {
 			return nil, err
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/workspaces.sql b/coderd/database/queries/workspaces.sql
@@ -82,6 +82,7 @@ SELECT
 	COALESCE(template_name.template_name, 'unknown') as template_name,
 	latest_build.template_version_id,
 	latest_build.template_version_name,
+	(upw.user_id IS NOT NULL)::boolean AS pinned,
 	COUNT(*) OVER () as count
 FROM
     workspaces
@@ -126,6 +127,19 @@ LEFT JOIN LATERAL (
 	WHERE
 		templates.id = workspaces.template_id
 ) template_name ON true
+LEFT JOIN LATERAL (
+	SELECT
+		user_id
+	FROM
+		user_pinned_workspaces
+	WHERE
+		workspaces.id = user_pinned_workspaces.workspace_id
+	AND
+		-- Omitting the owner_id parameter will result in
+		-- 00000000-0000-0000-0000-000000000000 which will not match
+		-- any rows in user_pinned_workspaces.
+		user_pinned_workspaces.user_id = @owner_id
+) upw ON TRUE
 WHERE
 	-- Optionally include deleted workspaces
 	workspaces.deleted = @deleted
@@ -262,6 +276,7 @@ WHERE
 	-- Authorize Filter clause will be injected below in GetAuthorizedWorkspaces
 	-- @authorize_filter
 ORDER BY
+	pinned DESC,
 	(latest_build.completed_at IS NOT NULL AND
 		latest_build.canceled_at IS NULL AND
 		latest_build.error IS NULL AND
