coder · mtojek · Mar 5, 2024 · Mar 4, 2024 · Mar 4, 2024 · Mar 4, 2024
diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
@@ -345,7 +345,7 @@ func mapAgentStatus(dbAgent database.WorkspaceAgent, agentInactiveDisconnectTime
 	return status
 }
 
-func (q *FakeQuerier) convertToWorkspaceRowsNoLock(ctx context.Context, workspaces []database.Workspace, count int64) []database.GetWorkspacesRow {
+func (q *FakeQuerier) convertToWorkspaceRowsNoLock(ctx context.Context, workspaces []database.Workspace, count int64, withSummary bool) []database.GetWorkspacesRow { //nolint:revive // withSummary flag ensures the extra technical row
 	rows := make([]database.GetWorkspacesRow, 0, len(workspaces))
 	for _, w := range workspaces {
 		wr := database.GetWorkspacesRow{
@@ -389,6 +389,12 @@ func (q *FakeQuerier) convertToWorkspaceRowsNoLock(ctx context.Context, workspac
 
 		rows = append(rows, wr)
 	}
+	if withSummary {
+		rows = append(rows, database.GetWorkspacesRow{
+			Name:  "**TECHNICAL_ROW**",
+			Count: count,
+		})
+	}
 	return rows
 }
 
@@ -8278,12 +8284,12 @@ func (q *FakeQuerier) GetAuthorizedWorkspaces(ctx context.Context, arg database.
 	}
 	if arg.Limit > 0 {
 		if int(arg.Limit) > len(workspaces) {
-			return q.convertToWorkspaceRowsNoLock(ctx, workspaces, int64(beforePageCount)), nil
+			return q.convertToWorkspaceRowsNoLock(ctx, workspaces, int64(beforePageCount), arg.WithSummary), nil
 		}
 		workspaces = workspaces[:arg.Limit]
 	}
 
-	return q.convertToWorkspaceRowsNoLock(ctx, workspaces, int64(beforePageCount)), nil
+	return q.convertToWorkspaceRowsNoLock(ctx, workspaces, int64(beforePageCount), arg.WithSummary), nil
 }
 
 func (q *FakeQuerier) GetAuthorizedUsers(ctx context.Context, arg database.GetUsersParams, prepared rbac.PreparedAuthorized) ([]database.GetUsersRow, error) {

diff --git a/coderd/database/modelqueries.go b/coderd/database/modelqueries.go
@@ -231,6 +231,7 @@ func (q *sqlQuerier) GetAuthorizedWorkspaces(ctx context.Context, arg GetWorkspa
 		arg.RequesterID,
 		arg.Offset,
 		arg.Limit,
+		arg.WithSummary,
 	)
 	if err != nil {
 		return nil, err
@@ -258,6 +259,11 @@ func (q *sqlQuerier) GetAuthorizedWorkspaces(ctx context.Context, arg GetWorkspa
 			&i.TemplateName,
 			&i.TemplateVersionID,
 			&i.TemplateVersionName,
+			&i.Username,
+			&i.LatestBuildCompletedAt,
+			&i.LatestBuildCanceledAt,
+			&i.LatestBuildError,
+			&i.LatestBuildTransition,
 			&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
@@ -77,12 +77,17 @@ WHERE
 	);
 
 -- name: GetWorkspaces :many
+WITH filtered_workspaces AS (
 SELECT
 	workspaces.*,
 	COALESCE(template.name, 'unknown') as template_name,
 	latest_build.template_version_id,
 	latest_build.template_version_name,
-	COUNT(*) OVER () as count
+	users.username as username,
+	latest_build.completed_at as latest_build_completed_at,
+	latest_build.canceled_at as latest_build_canceled_at,
+	latest_build.error as latest_build_error,
+	latest_build.transition as latest_build_transition
 FROM
     workspaces
 JOIN
@@ -266,23 +271,75 @@ WHERE
 	END
 	-- Authorize Filter clause will be injected below in GetAuthorizedWorkspaces
 	-- @authorize_filter
-ORDER BY
-	-- To ensure that 'favorite' workspaces show up first in the list only for their owner.
-	CASE WHEN workspaces.owner_id = @requester_id AND workspaces.favorite THEN 0 ELSE 1 END ASC,
-	(latest_build.completed_at IS NOT NULL AND
-		latest_build.canceled_at IS NULL AND
-		latest_build.error IS NULL AND
-		latest_build.transition = 'start'::workspace_transition) DESC,
-	LOWER(users.username) ASC,
-	LOWER(workspaces.name) ASC
-LIMIT
-	CASE
-		WHEN @limit_ :: integer > 0 THEN
-			@limit_
-	END
-OFFSET
-	@offset_
-;
+), filtered_workspaces_order AS (
+	SELECT
+		fw.*
+	FROM
+		filtered_workspaces fw
+	ORDER BY
+		-- To ensure that 'favorite' workspaces show up first in the list only for their owner.
+		CASE WHEN owner_id = @requester_id AND favorite THEN 0 ELSE 1 END ASC,
+		(latest_build_completed_at IS NOT NULL AND
+			latest_build_canceled_at IS NULL AND
+			latest_build_error IS NULL AND
+			latest_build_transition = 'start'::workspace_transition) DESC,
+		LOWER(username) ASC,
+		LOWER(name) ASC
+	LIMIT
+		CASE
+			WHEN @limit_ :: integer > 0 THEN
+				@limit_
+		END
+	OFFSET
+		@offset_
+), filtered_workspaces_order_with_summary AS (
+	SELECT
+		fwo.*
+	FROM
+		filtered_workspaces_order fwo
+	-- Return a technical summary row with total count of workspaces.
+	-- It is used to present the correct count if pagination goes beyond the offset.
+	UNION ALL
+	SELECT
+		'00000000-0000-0000-0000-000000000000'::uuid, -- id
+		'0001-01-01 00:00:00+00'::timestamp, -- created_at
+		'0001-01-01 00:00:00+00'::timestamp, -- updated_at
+		'00000000-0000-0000-0000-000000000000'::uuid, -- owner_id
+		'00000000-0000-0000-0000-000000000000'::uuid, -- organization_id
+		'00000000-0000-0000-0000-000000000000'::uuid, -- template_id
+		false, -- deleted
+		'**TECHNICAL_ROW**', -- name
+		'', -- autostart_schedule
+		0, -- ttl
+		'0001-01-01 00:00:00+00'::timestamp, -- last_used_at
+		'0001-01-01 00:00:00+00'::timestamp, -- dormant_at
+		'0001-01-01 00:00:00+00'::timestamp, -- deleting_at
+		'never'::automatic_updates, -- automatic_updates
+		false, -- favorite
+		-- Extra columns added to `filtered_workspaces`
+		'', -- template_name
+		'00000000-0000-0000-0000-000000000000'::uuid, -- template_version_id
+		'', -- template_version_name
+		'', -- username
+		'0001-01-01 00:00:00+00'::timestamp, -- latest_build_completed_at,
+		'0001-01-01 00:00:00+00'::timestamp, -- latest_build_canceled_at,
+		'', -- latest_build_error
+		'start'::workspace_transition -- latest_build_transition
+	WHERE
+		@with_summary :: boolean = true
+), total_count AS (
+	SELECT
+		count(*) AS count
+    FROM
+		filtered_workspaces
+)
+SELECT
+	fwos.*,
+	tc.count
+FROM
+	filtered_workspaces_order_with_summary fwos
+CROSS JOIN
+	total_count tc;
 
 -- name: GetWorkspaceByOwnerIDAndName :one
 SELECT

diff --git a/coderd/workspaces.go b/coderd/workspaces.go
@@ -173,6 +173,9 @@ func (api *API) workspaces(rw http.ResponseWriter, r *http.Request) {
 	// the workspace owner_id when ordering the rows.
 	filter.RequesterID = apiKey.UserID
 
+	// We need the technical row to present the correct count on every page.
+	filter.WithSummary = true
+
 	workspaceRows, err := api.Database.GetAuthorizedWorkspaces(ctx, filter, prepared)
 	if err != nil {
 		httpapi.Write(ctx, rw, http.StatusInternalServerError, codersdk.Response{
@@ -181,6 +184,23 @@ func (api *API) workspaces(rw http.ResponseWriter, r *http.Request) {
 		})
 		return
 	}
+	if len(workspaceRows) == 0 {
+		httpapi.Write(ctx, rw, http.StatusInternalServerError, codersdk.Response{
+			Message: "Internal error fetching workspaces.",
+			Detail:  "Workspace summary row is missing.",
+		})
+		return
+	}
+	if len(workspaceRows) == 1 {
+		httpapi.Write(ctx, rw, http.StatusOK, codersdk.WorkspacesResponse{
+			Workspaces: []codersdk.Workspace{},
+			Count:      int(workspaceRows[0].Count),
+		})
+		return
+	}
+	// Skip technical summary row
+	workspaceRows = workspaceRows[:len(workspaceRows)-1]
+
 	if len(workspaceRows) == 0 {
 		httpapi.Write(ctx, rw, http.StatusOK, codersdk.WorkspacesResponse{
 			Workspaces: []codersdk.Workspace{},

diff --git a/coderd/workspaces_test.go b/coderd/workspaces_test.go
@@ -1753,6 +1753,7 @@ func TestOffsetLimit(t *testing.T) {
 	})
 	require.NoError(t, err)
 	require.Len(t, ws.Workspaces, 0)
+	require.Equal(t, ws.Count, 3) // can't find workspaces, but count is non-zero
 }
 
 func TestWorkspaceUpdateAutostart(t *testing.T) {