coder · Emyrk · Jan 23, 2024 · Jan 19, 2024 · Jan 19, 2024 · Jan 19, 2024
diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
@@ -7534,6 +7534,23 @@ func (q *FakeQuerier) GetAuthorizedWorkspaces(ctx context.Context, arg database.
 			}
 		}
 
+		if arg.UsingActive.Valid {
+			build, err := q.getLatestWorkspaceBuildByWorkspaceIDNoLock(ctx, workspace.ID)
+			if err != nil {
+				return nil, xerrors.Errorf("get latest build: %w", err)
+			}
+
+			template, err := q.getTemplateByIDNoLock(ctx, workspace.TemplateID)
+			if err != nil {
+				return nil, xerrors.Errorf("get template: %w", err)
+			}
+
+			updated := build.TemplateVersionID == template.ActiveVersionID
+			if arg.UsingActive.Bool != updated {
+				continue
+			}
+		}
+
 		if !arg.Deleted && workspace.Deleted {
 			continue
 		}

diff --git a/coderd/database/modelqueries.go b/coderd/database/modelqueries.go
@@ -198,7 +198,7 @@ type workspaceQuerier interface {
 // This code is copied from `GetWorkspaces` and adds the authorized filter WHERE
 // clause.
 func (q *sqlQuerier) GetAuthorizedWorkspaces(ctx context.Context, arg GetWorkspacesParams, prepared rbac.PreparedAuthorized) ([]GetWorkspacesRow, error) {
-	authorizedFilter, err := prepared.CompileToSQL(ctx, rbac.ConfigWithoutACL())
+	authorizedFilter, err := prepared.CompileToSQL(ctx, rbac.ConfigWorkspaces())
 	if err != nil {
 		return nil, xerrors.Errorf("compile authorized filter: %w", err)
 	}
@@ -225,6 +225,7 @@ func (q *sqlQuerier) GetAuthorizedWorkspaces(ctx context.Context, arg GetWorkspa
 		arg.Dormant,
 		arg.LastUsedBefore,
 		arg.LastUsedAfter,
+		arg.UsingActive,
 		arg.Offset,
 		arg.Limit,
 	)

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
@@ -79,7 +79,7 @@ WHERE
 -- name: GetWorkspaces :many
 SELECT
 	workspaces.*,
-	COALESCE(template_name.template_name, 'unknown') as template_name,
+	COALESCE(template.name, 'unknown') as template_name,
 	latest_build.template_version_id,
 	latest_build.template_version_name,
 	COUNT(*) OVER () as count
@@ -120,12 +120,12 @@ LEFT JOIN LATERAL (
 ) latest_build ON TRUE
 LEFT JOIN LATERAL (
 	SELECT
-		templates.name AS template_name
+		*
 	FROM
 		templates
 	WHERE
 		templates.id = workspaces.template_id
-) template_name ON true
+) template ON true
 WHERE
 	-- Optionally include deleted workspaces
 	workspaces.deleted = @deleted
@@ -259,6 +259,11 @@ WHERE
 				  workspaces.last_used_at >= @last_used_after
 		  ELSE true
 	END
+  	AND CASE
+		  WHEN sqlc.narg('using_active') :: boolean IS NOT NULL THEN
+			  (latest_build.template_version_id = template.active_version_id) = sqlc.narg('using_active') :: boolean
+		  ELSE true
+	END
 	-- Authorize Filter clause will be injected below in GetAuthorizedWorkspaces
 	-- @authorize_filter
 ORDER BY

diff --git a/coderd/rbac/authz.go b/coderd/rbac/authz.go
@@ -611,6 +611,12 @@ func ConfigWithoutACL() regosql.ConvertConfig {
 	}
 }
 
+func ConfigWorkspaces() regosql.ConvertConfig {
+	return regosql.ConvertConfig{
+		VariableConverter: regosql.WorkspaceConverter(),
+	}
+}
+
 func Compile(cfg regosql.ConvertConfig, pa *PartialAuthorizer) (AuthorizeFilter, error) {
 	root, err := regosql.ConvertRegoAst(cfg, pa.partialQueries)
 	if err != nil {

diff --git a/coderd/rbac/regosql/configs.go b/coderd/rbac/regosql/configs.go
@@ -53,6 +53,20 @@ func UserConverter() *sqltypes.VariableConverter {
 	return matcher
 }
 
+func WorkspaceConverter() *sqltypes.VariableConverter {
+	matcher := sqltypes.NewVariableConverter().RegisterMatcher(
+		resourceIDMatcher(),
+		sqltypes.StringVarMatcher("workspaces.organization_id :: text", []string{"input", "object", "org_owner"}),
+		userOwnerMatcher(),
+	)
+	matcher.RegisterMatcher(
+		sqltypes.AlwaysFalse(groupACLMatcher(matcher)),
+		sqltypes.AlwaysFalse(userACLMatcher(matcher)),
+	)
+
+	return matcher
+}
+
 // NoACLConverter should be used when the target SQL table does not contain
 // group or user ACL columns.
 func NoACLConverter() *sqltypes.VariableConverter {

diff --git a/coderd/searchquery/search.go b/coderd/searchquery/search.go
@@ -1,6 +1,7 @@
 package searchquery
 
 import (
+	"database/sql"
 	"fmt"
 	"net/url"
 	"strings"
@@ -110,6 +111,14 @@ func Workspaces(query string, page codersdk.Pagination, agentInactiveDisconnectT
 	filter.Dormant = parser.Boolean(values, false, "dormant")
 	filter.LastUsedAfter = parser.Time3339Nano(values, time.Time{}, "last_used_after")
 	filter.LastUsedBefore = parser.Time3339Nano(values, time.Time{}, "last_used_before")
+	filter.UsingActive = sql.NullBool{
+		// Invert the value of the query parameter to get the correct value.
+		// UsingActive returns if the workspace is on the latest template active version.
+		Bool: !parser.Boolean(values, true, "outdated"),
+		// Only include this search term if it was provided. Otherwise default to omitting it
+		// which will return all workspaces.
+		Valid: values.Has("outdated"),
+	}
 
 	parser.ErrorExcessParams(values)
 	return filter, parser.Errors

diff --git a/coderd/searchquery/search_test.go b/coderd/searchquery/search_test.go
@@ -1,6 +1,7 @@
 package searchquery_test
 
 import (
+	"database/sql"
 	"fmt"
 	"strings"
 	"testing"
@@ -116,7 +117,26 @@ func TestSearchWorkspace(t *testing.T) {
 				OwnerUsername: "foo",
 			},
 		},
-
+		{
+			Name:  "Outdated",
+			Query: `outdated:true`,
+			Expected: database.GetWorkspacesParams{
+				UsingActive: sql.NullBool{
+					Bool:  false,
+					Valid: true,
+				},
+			},
+		},
+		{
+			Name:  "Updated",
+			Query: `outdated:false`,
+			Expected: database.GetWorkspacesParams{
+				UsingActive: sql.NullBool{
+					Bool:  true,
+					Valid: true,
+				},
+			},
+		},
 		// Failures
 		{
 			Name:                  "NoPrefix",

diff --git a/coderd/workspaces_test.go b/coderd/workspaces_test.go
@@ -1632,6 +1632,56 @@ func TestWorkspaceFilterManual(t *testing.T) {
 		require.Len(t, afterRes.Workspaces, 1)
 		require.Equal(t, after.ID, afterRes.Workspaces[0].ID)
 	})
+	t.Run("Updated", 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.AwaitTemplateVersionJobCompleted(t, client, version.ID)
+		template := coderdtest.CreateTemplate(t, client, user.OrganizationID, version.ID)
+		workspace := coderdtest.CreateWorkspace(t, client, user.OrganizationID, template.ID)
+
+		ctx, cancel := context.WithTimeout(context.Background(), testutil.WaitLong)
+		defer cancel()
+
+		// Workspace is up-to-date
+		res, err := client.Workspaces(ctx, codersdk.WorkspaceFilter{
+			FilterQuery: "outdated:false",
+		})
+		require.NoError(t, err)
+		require.Len(t, res.Workspaces, 1)
+		require.Equal(t, workspace.ID, res.Workspaces[0].ID)
+
+		res, err = client.Workspaces(ctx, codersdk.WorkspaceFilter{
+			FilterQuery: "outdated:true",
+		})
+		require.NoError(t, err)
+		require.Len(t, res.Workspaces, 0)
+
+		// Now make it out of date
+		newTv := coderdtest.CreateTemplateVersion(t, client, user.OrganizationID, nil, func(request *codersdk.CreateTemplateVersionRequest) {
+			request.TemplateID = template.ID
+		})
+		coderdtest.AwaitTemplateVersionJobCompleted(t, client, version.ID)
+		err = client.UpdateActiveTemplateVersion(ctx, template.ID, codersdk.UpdateActiveTemplateVersion{
+			ID: newTv.ID,
+		})
+		require.NoError(t, err)
+
+		// Check the query again
+		res, err = client.Workspaces(ctx, codersdk.WorkspaceFilter{
+			FilterQuery: "outdated:false",
+		})
+		require.NoError(t, err)
+		require.Len(t, res.Workspaces, 0)
+
+		res, err = client.Workspaces(ctx, codersdk.WorkspaceFilter{
+			FilterQuery: "outdated:true",
+		})
+		require.NoError(t, err)
+		require.Len(t, res.Workspaces, 1)
+		require.Equal(t, workspace.ID, res.Workspaces[0].ID)
+	})
 }
 
 func TestOffsetLimit(t *testing.T) {

diff --git a/site/src/pages/WorkspacesPage/filter/filter.tsx b/site/src/pages/WorkspacesPage/filter/filter.tsx
@@ -22,6 +22,7 @@ export const workspaceFilterQuery = {
   running: "status:running",
   failed: "status:failed",
   dormant: "dormant:true",
+  outdated: "outdated:true",
 };
 
 type FilterPreset = {
@@ -48,6 +49,10 @@ const PRESET_FILTERS: FilterPreset[] = [
     query: workspaceFilterQuery.failed,
     name: "Failed workspaces",
   },
+  {
+    query: workspaceFilterQuery.outdated,
+    name: "Outdated workspaces",
+  },
 ];
 
 // Defined outside component so that the array doesn't get reconstructed each render