Update sql and add test - wip

coder · presleyp · Oct 11, 2022 · Sep 22, 2022 · Sep 22, 2022 · Sep 22, 2022
commit 8500dbfaf798cd8c0fa1a7db10c01f022df789db
diff --git a/coderd/database/queries/workspaces.sql b/coderd/database/queries/workspaces.sql
@@ -10,27 +10,44 @@ LIMIT
 
 -- name: GetWorkspaces :many
 SELECT
-    *
+    workspaces.*
 FROM
     workspaces
+LEFT JOIN LATERAL (
+	SELECT
+		*
+	FROM
+		workspace_builds
+	WHERE
+		workspace_builds.workspace_id = workspaces.id
+	ORDER BY
+		build_number DESC
+	LIMIT
+		1
+) latest_build ON TRUE
 WHERE
     -- Optionally include deleted workspaces
 	workspaces.deleted = @deleted
+	AND CASE
+		WHEN @status :: text != '' THEN
+			latest_build.transition = convertStatus(@status)
+		ELSE true
+	END
 	-- Filter by owner_id
 	AND CASE
 		WHEN @owner_id :: uuid != '00000000-00000000-00000000-00000000' THEN
 			owner_id = @owner_id
 		ELSE true
 	END
-  	-- Filter by owner_name
+	-- Filter by owner_name
 	AND CASE
 		WHEN @owner_username :: text != '' THEN
 			owner_id = (SELECT id FROM users WHERE lower(username) = lower(@owner_username))
 		ELSE true
 	END
 	-- Filter by template_name
 	-- There can be more than 1 template with the same name across organizations.
-  	-- Use the organization filter to restrict to 1 org if needed.
+	-- Use the organization filter to restrict to 1 org if needed.
 	AND CASE
 		WHEN @template_name :: text != '' THEN
 			template_id = ANY(SELECT id FROM templates WHERE lower(name) = lower(@template_name))
@@ -45,7 +62,7 @@ WHERE
 	-- Filter by name, matching on substring
 	AND CASE
 		WHEN @name :: text != '' THEN
-		    name ILIKE '%' || @name || '%'
+			name ILIKE '%' || @name || '%'
 		ELSE true
 	END
 ;

diff --git a/coderd/workspaces_test.go b/coderd/workspaces_test.go
@@ -711,6 +711,53 @@ func TestWorkspaceFilterManual(t *testing.T) {
 		require.Len(t, ws, 1)
 		require.Equal(t, workspace.ID, ws[0].ID)
 	})
+	t.Run("Status", func(t *testing.T) {
+		t.Parallel()
+		client := coderdtest.New(t, &coderdtest.Options{IncludeProvisionerDaemon: true})
+		user := coderdtest.CreateFirstUser(t, client)
+		version := coderdtest.CreateTemplateVersion(t, client, user.OrganizationID, nil)
+		coderdtest.AwaitTemplateVersionJob(t, client, version.ID)
+		template := coderdtest.CreateTemplate(t, client, user.OrganizationID, version.ID)
+		workspace1 := coderdtest.CreateWorkspace(t, client, user.OrganizationID, template.ID)
+		workspace2 := coderdtest.CreateWorkspace(t, client, user.OrganizationID, template.ID)
+
+		// wait for workspaces to be "running"
+		_ = coderdtest.AwaitWorkspaceBuildJob(t, client, workspace1.LatestBuild.ID)
+		_ = coderdtest.AwaitWorkspaceBuildJob(t, client, workspace2.LatestBuild.ID)
+
+		ctx, cancel := context.WithTimeout(context.Background(), testutil.WaitLong)
+		defer cancel()
+
+		// filter finds both running workspaces
+		ws, err := client.Workspaces(ctx, codersdk.WorkspaceFilter{
+			Status: "running",
+		})
+		require.NoError(t, err)
+		require.Len(t, ws, 2)
+
+		// stop workspace1
+		build1 := coderdtest.CreateWorkspaceBuild(t, client, workspace1, database.WorkspaceTransitionStop)
+		_ = coderdtest.AwaitWorkspaceBuildJob(t, client, build1.ID)
+
+		// filter finds one running workspace
+		ws, err := client.Workspaces(ctx, codersdk.WorkspaceFilter{
+			Status: "running",
+		})
+		require.NoError(t, err)
+		require.Len(t, ws, 1)
+		require.Equal(t, workspace2.ID, ws[0].ID)
+
+		// stop workspace2
+		build2 := coderdtest.CreateWorkspaceBuild(t, client, workspace2, database.WorkspaceTransitionStop)
+		_ = coderdtest.AwaitWorkspaceBuildJob(t, client, build2.ID)
+
+		// filter finds no running workspaces
+		ws, err = client.Workspaces(ctx, codersdk.WorkspaceFilter{
+			Status: "running",
+		})
+		require.NoError(t, err)
+		require.Len(t, ws, 0)
+	})
 	t.Run("FilterQuery", func(t *testing.T) {
 		t.Parallel()
 		client := coderdtest.New(t, &coderdtest.Options{IncludeProvisionerDaemon: true})