query

hugodutka · hugodutka · commit af7ef3b11b45 · 2025-06-16T14:09:33.000Z
diff --git a/coderd/database/queries/templates.sql b/coderd/database/queries/templates.sql
@@ -10,50 +10,58 @@ LIMIT
 
 -- name: GetTemplatesWithFilter :many
 SELECT
-	*
+	t.*
 FROM
-	template_with_names AS templates
+	template_with_names AS t
+LEFT JOIN
+	template_versions tv ON t.active_version_id = tv.id
 WHERE
 	-- Optionally include deleted templates
-	templates.deleted = @deleted
+	t.deleted = @deleted
 	-- Filter by organization_id
 	AND CASE
 		WHEN @organization_id :: uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN
-			organization_id = @organization_id
+			t.organization_id = @organization_id
 		ELSE true
 	END
 	-- Filter by exact name
 	AND CASE
 		WHEN @exact_name :: text != '' THEN
-			LOWER("name") = LOWER(@exact_name)
+			LOWER(t.name) = LOWER(@exact_name)
 		ELSE true
 	END
 	-- Filter by name, matching on substring
 	AND CASE
 		WHEN @fuzzy_name :: text != '' THEN
-			lower(name) ILIKE '%' || lower(@fuzzy_name) || '%'
+			lower(t.name) ILIKE '%' || lower(@fuzzy_name) || '%'
 		ELSE true
 	END
 	-- Filter by ids
 	AND CASE
 		WHEN array_length(@ids :: uuid[], 1) > 0 THEN
-			id = ANY(@ids)
+			t.id = ANY(@ids)
 		ELSE true
 	END
 	-- Filter by deprecated
 	AND CASE
 		WHEN sqlc.narg('deprecated') :: boolean IS NOT NULL THEN
 			CASE
 				WHEN sqlc.narg('deprecated') :: boolean THEN
-					deprecated != ''
+					t.deprecated != ''
 				ELSE
-					deprecated = ''
+					t.deprecated = ''
 			END
 		ELSE true
 	END
+	-- Filter by has_ai_task in latest version
+	AND CASE
+		WHEN sqlc.narg('has_ai_task') :: boolean IS NOT NULL THEN
+			tv.has_ai_task = sqlc.narg('has_ai_task') :: boolean
+		ELSE true
+	END
   -- Authorize Filter clause will be injected below in GetAuthorizedTemplates
   -- @authorize_filter
-ORDER BY (name, id) ASC
+ORDER BY (t.name, t.id) ASC
 ;
 
 -- name: GetTemplateByOrganizationAndName :one
diff --git a/coderd/database/queries/workspaces.sql b/coderd/database/queries/workspaces.sql
@@ -116,7 +116,8 @@ SELECT
 	latest_build.canceled_at as latest_build_canceled_at,
 	latest_build.error as latest_build_error,
 	latest_build.transition as latest_build_transition,
-	latest_build.job_status as latest_build_status
+	latest_build.job_status as latest_build_status,
+	latest_build.has_ai_task as latest_build_has_ai_task
 FROM
 	workspaces_expanded as workspaces
 JOIN
@@ -128,6 +129,7 @@ LEFT JOIN LATERAL (
 		workspace_builds.id,
 		workspace_builds.transition,
 		workspace_builds.template_version_id,
+		workspace_builds.has_ai_task,
 		template_versions.name AS template_version_name,
 		provisioner_jobs.id AS provisioner_job_id,
 		provisioner_jobs.started_at,
@@ -345,6 +347,12 @@ WHERE
 			  (latest_build.template_version_id = template.active_version_id) = sqlc.narg('using_active') :: boolean
 		  ELSE true
 	END
+	-- Filter by has_ai_task in latest build
+	AND CASE
+		WHEN sqlc.narg('has_ai_task') :: boolean IS NOT NULL THEN
+			latest_build.has_ai_task = sqlc.narg('has_ai_task') :: boolean
+		ELSE true
+	END
 	-- Authorize Filter clause will be injected below in GetAuthorizedWorkspaces
 	-- @authorize_filter
 ), filtered_workspaces_order AS (
@@ -411,7 +419,8 @@ WHERE
 		'0001-01-01 00:00:00+00'::timestamptz, -- latest_build_canceled_at,
 		'', -- latest_build_error
 		'start'::workspace_transition, -- latest_build_transition
-		'unknown'::provisioner_job_status -- latest_build_status
+		'unknown'::provisioner_job_status, -- latest_build_status
+		false -- latest_build_has_ai_task
 	WHERE
 		@with_summary :: boolean = true
 ), total_count AS (
diff --git a/coderd/database/sqlc.yaml b/coderd/database/sqlc.yaml
@@ -149,6 +149,7 @@ sql:
           stale_interval_ms: StaleIntervalMS
           has_ai_task: HasAITask
           ai_tasks_sidebar_app_id: AITasksSidebarAppID
+          latest_build_has_ai_task: LatestBuildHasAITask
 rules:
   - name: do-not-use-public-schema-in-queries
     message: "do not use public schema in queries"
