coder · mtojek · Nov 24, 2022 · Nov 21, 2022 · Nov 22, 2022 · Nov 22, 2022
diff --git a/coderd/database/databasefake/databasefake.go b/coderd/database/databasefake/databasefake.go
@@ -871,6 +871,55 @@ func (q *fakeQuerier) GetAuthorizedWorkspaces(ctx context.Context, arg database.
 			}
 		}
 
+		if arg.HasAgent != "" {
+			build, err := q.GetLatestWorkspaceBuildByWorkspaceID(ctx, workspace.ID)
+			if err != nil {
+				return nil, xerrors.Errorf("get latest build: %w", err)
+			}
+
+			job, err := q.GetProvisionerJobByID(ctx, build.JobID)
+			if err != nil {
+				return nil, xerrors.Errorf("get provisioner job: %w", err)
+			}
+
+			workspaceResources, err := q.GetWorkspaceResourcesByJobID(ctx, job.ID)
+			if err != nil {
+				return nil, xerrors.Errorf("get workspace resources: %w", err)
+			}
+
+			var workspaceResourceIDs []uuid.UUID
+			for _, wr := range workspaceResources {
+				workspaceResourceIDs = append(workspaceResourceIDs, wr.ID)
+			}
+
+			workspaceAgents, err := q.GetWorkspaceAgentsByResourceIDs(ctx, workspaceResourceIDs)
+			if err != nil {
+				return nil, xerrors.Errorf("get workspace agents: %w", err)
+			}
+
+			var hasAgentMatched bool
+			for _, wa := range workspaceAgents {
+				switch arg.HasAgent {
+				case "connected":
+					hasAgentMatched = wa.LastConnectedAt.Valid
+				case "connecting":
+					hasAgentMatched = !wa.FirstConnectedAt.Valid
+				case "disconnected":
+					// FIXME agentInactiveDisconnectTimeout = 6
+					hasAgentMatched = (wa.DisconnectedAt.Valid && wa.DisconnectedAt.Time.After(wa.LastConnectedAt.Time)) ||
+						(wa.LastConnectedAt.Valid && wa.LastConnectedAt.Time.Add(6*time.Second).Before(database.Now()))
+				case "timeout":
+					hasAgentMatched = !wa.FirstConnectedAt.Valid &&
+						wa.CreatedAt.Add(time.Duration(wa.ConnectionTimeoutSeconds)*time.Second).Before(database.Now())
+				}
+				break // only 1 agent is expected
+			}
+
+			if !hasAgentMatched {
+				continue
+			}
+		}
+
 		if len(arg.TemplateIds) > 0 {
 			match := false
 			for _, id := range arg.TemplateIds {

diff --git a/coderd/database/modelqueries.go b/coderd/database/modelqueries.go
@@ -132,6 +132,7 @@ func (q *sqlQuerier) GetAuthorizedWorkspaces(ctx context.Context, arg GetWorkspa
 		arg.TemplateName,
 		pq.Array(arg.TemplateIds),
 		arg.Name,
+		arg.HasAgent,
 		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
@@ -44,22 +44,39 @@ FROM
 	workspaces
 LEFT JOIN LATERAL (
 	SELECT
+	    build_number,
+	    workspace_builds.job_id,
 		workspace_builds.transition,
 		provisioner_jobs.started_at,
 		provisioner_jobs.updated_at,
 		provisioner_jobs.canceled_at,
 		provisioner_jobs.completed_at,
-		provisioner_jobs.error
+		provisioner_jobs.error,
+		workspace_agents.created_at,
+		workspace_agents.disconnected_at,
+		workspace_agents.first_connected_at,
+		workspace_agents.last_connected_at,
+		workspace_agents.connection_timeout_seconds
 	FROM
 		workspace_builds
 	LEFT JOIN
 		provisioner_jobs
 	ON
 		provisioner_jobs.id = workspace_builds.job_id
+	LEFT JOIN
+	    workspace_resources
+	ON
+	    workspace_resources.job_id = provisioner_jobs.id
+	LEFT JOIN
+	    workspace_agents
+	ON
+	    workspace_agents.resource_id = workspace_resources.id
 	WHERE
 		workspace_builds.workspace_id = workspaces.id
 	ORDER BY
-		build_number DESC
+		build_number DESC,
+		workspace_agents.last_connected_at ASC,
+		workspace_agents.first_connected_at ASC
 	LIMIT
 		1
 ) latest_build ON TRUE
@@ -161,6 +178,30 @@ WHERE
 			name ILIKE '%' || @name || '%'
 		ELSE true
 	END
+	-- Filter by agent status
+	-- has-agent: is only applicable for workspaces in "start" transition. Stopped and deleted workspaces don't have agents.
+	AND CASE
+		WHEN @has_agent :: text != '' THEN
+			latest_build.transition = 'start'::workspace_transition
+			AND CASE
+				WHEN @has_agent = 'timeout' THEN
+					latest_build.first_connected_at IS NULL AND (latest_build.created_at + latest_build.connection_timeout_seconds * INTERVAL '1 second' < NOW())
+				WHEN @has_agent = 'connecting' THEN
+			    	latest_build.first_connected_at IS NULL
+				WHEN @has_agent = 'disconnected' THEN
+					(
+						latest_build.disconnected_at IS NOT NULL AND
+						latest_build.disconnected_at > latest_build.last_connected_at
+					) OR (
+						latest_build.last_connected_at IS NOT NULL AND
+						latest_build.last_connected_at + 6 * INTERVAL '1 second' < NOW() -- FIXME agentInactiveDisconnectTimeout = 6
+					)
+				WHEN @has_agent = 'connected' THEN
+			    	latest_build.last_connected_at IS NOT NULL
+				ELSE true
+			END
+		ELSE true
+	END
 	-- Authorize Filter clause will be injected below in GetAuthorizedWorkspaces
 	-- @authorize_filter
 ORDER BY

diff --git a/coderd/workspaces.go b/coderd/workspaces.go
@@ -1146,7 +1146,7 @@ func workspaceSearchQuery(query string, page codersdk.Pagination) (database.GetW
 	filter.TemplateName = parser.String(searchParams, "", "template")
 	filter.Name = parser.String(searchParams, "", "name")
 	filter.Status = parser.String(searchParams, "", "status")
-
+	filter.HasAgent = parser.String(searchParams, "", "has-agent")
 	return filter, parser.Errors
 }