Skip to content

Commit e20c3c8

Browse files
hugodutkahugodutka
committed
query
1 parent 44d4646 commit e20c3c8

File tree

3 files changed

+45
-12
lines changed

3 files changed

+45
-12
lines changed

‎coderd/database/queries/templates.sql

Lines changed: 18 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -10,50 +10,58 @@ LIMIT
1010

1111
-- name: GetTemplatesWithFilter :many
1212
SELECT
13-
*
13+
t.*
1414
FROM
15-
template_with_names AS templates
15+
template_with_names AS t
16+
LEFT JOIN
17+
template_versions tv ON t.active_version_id = tv.id
1618
WHERE
1719
-- Optionally include deleted templates
18-
templates.deleted = @deleted
20+
t.deleted = @deleted
1921
-- Filter by organization_id
2022
AND CASE
2123
WHEN @organization_id :: uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN
22-
organization_id = @organization_id
24+
t.organization_id = @organization_id
2325
ELSE true
2426
END
2527
-- Filter by exact name
2628
AND CASE
2729
WHEN @exact_name :: text != '' THEN
28-
LOWER("name") = LOWER(@exact_name)
30+
LOWER(t.name) = LOWER(@exact_name)
2931
ELSE true
3032
END
3133
-- Filter by name, matching on substring
3234
AND CASE
3335
WHEN @fuzzy_name :: text != '' THEN
34-
lower(name) ILIKE '%' || lower(@fuzzy_name) || '%'
36+
lower(t.name) ILIKE '%' || lower(@fuzzy_name) || '%'
3537
ELSE true
3638
END
3739
-- Filter by ids
3840
AND CASE
3941
WHEN array_length(@ids :: uuid[], 1) > 0 THEN
40-
id = ANY(@ids)
42+
t.id = ANY(@ids)
4143
ELSE true
4244
END
4345
-- Filter by deprecated
4446
AND CASE
4547
WHEN sqlc.narg('deprecated') :: boolean IS NOT NULL THEN
4648
CASE
4749
WHEN sqlc.narg('deprecated') :: boolean THEN
48-
deprecated != ''
50+
t.deprecated != ''
4951
ELSE
50-
deprecated = ''
52+
t.deprecated = ''
5153
END
5254
ELSE true
5355
END
56+
-- Filter by has_ai_task in latest version
57+
AND CASE
58+
WHEN sqlc.narg('has_ai_task') :: boolean IS NOT NULL THEN
59+
tv.has_ai_task = sqlc.narg('has_ai_task') :: boolean
60+
ELSE true
61+
END
5462
-- Authorize Filter clause will be injected below in GetAuthorizedTemplates
5563
-- @authorize_filter
56-
ORDER BY (name, id) ASC
64+
ORDER BY (t.name, t.id) ASC
5765
;
5866

5967
-- name: GetTemplateByOrganizationAndName :one

‎coderd/database/queries/workspaces.sql

Lines changed: 26 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -116,7 +116,8 @@ SELECT
116116
latest_build.canceled_at as latest_build_canceled_at,
117117
latest_build.error as latest_build_error,
118118
latest_build.transition as latest_build_transition,
119-
latest_build.job_status as latest_build_status
119+
latest_build.job_status as latest_build_status,
120+
latest_build.has_ai_task as latest_build_has_ai_task
120121
FROM
121122
workspaces_expanded as workspaces
122123
JOIN
@@ -128,6 +129,7 @@ LEFT JOIN LATERAL (
128129
workspace_builds.id,
129130
workspace_builds.transition,
130131
workspace_builds.template_version_id,
132+
workspace_builds.has_ai_task,
131133
template_versions.name AS template_version_name,
132134
provisioner_jobs.id AS provisioner_job_id,
133135
provisioner_jobs.started_at,
@@ -345,6 +347,27 @@ WHERE
345347
(latest_build.template_version_id = template.active_version_id) = sqlc.narg('using_active') :: boolean
346348
ELSE true
347349
END
350+
-- Filter by has_ai_task in latest build
351+
AND CASE
352+
WHEN sqlc.narg('has_ai_task') :: boolean IS NOT NULL THEN
353+
(COALESCE(latest_build.has_ai_task, false) OR (
354+
-- If the build has no AI task, it means that the provisioner job is in progress
355+
-- and we don't know if it has an AI task yet. In this case, we optimistically
356+
-- assume that it has an AI task if the AI Prompt parameter is not empty. This
357+
-- lets the AI Task frontend spawn a task and see it immediately after instead of
358+
-- having to wait for the build to complete.
359+
latest_build.has_ai_task IS NULL AND
360+
latest_build.completed_at IS NULL AND
361+
EXISTS (
362+
SELECT 1
363+
FROM workspace_build_parameters
364+
WHERE workspace_build_parameters.workspace_build_id = latest_build.id
365+
AND workspace_build_parameters.name = 'AI Prompt'
366+
AND workspace_build_parameters.value != ''
367+
)
368+
)) = (sqlc.narg('has_ai_task') :: boolean)
369+
ELSE true
370+
END
348371
-- Authorize Filter clause will be injected below in GetAuthorizedWorkspaces
349372
-- @authorize_filter
350373
), filtered_workspaces_order AS (
@@ -411,7 +434,8 @@ WHERE
411434
'0001-01-01 00:00:00+00'::timestamptz, -- latest_build_canceled_at,
412435
'', -- latest_build_error
413436
'start'::workspace_transition, -- latest_build_transition
414-
'unknown'::provisioner_job_status -- latest_build_status
437+
'unknown'::provisioner_job_status, -- latest_build_status
438+
false -- latest_build_has_ai_task
415439
WHERE
416440
@with_summary :: boolean = true
417441
), total_count AS (

‎coderd/database/sqlc.yaml

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -149,6 +149,7 @@ sql:
149149
stale_interval_ms: StaleIntervalMS
150150
has_ai_task: HasAITask
151151
ai_tasks_sidebar_app_id: AITasksSidebarAppID
152+
latest_build_has_ai_task: LatestBuildHasAITask
152153
rules:
153154
- name: do-not-use-public-schema-in-queries
154155
message: "do not use public schema in queries"

0 commit comments

Comments
 (0)