@@ -49,18 +49,59 @@ WHERE tvp.desired_instances IS NOT NULL -- Consider only presets that have a pre
4949AND (t .id = sqlc .narg (' template_id' OR sqlc .narg (' template_id' NULL );
5050
5151-- name: GetRunningPrebuiltWorkspaces :many
52+ WITH latest_prebuildsAS (
53+ SELECT
54+ latest_build .workspace_id ,
55+ workspaces .name ,
56+ workspaces .template_id ,
57+ latest_build .template_version_id ,
58+ latest_build .template_version_preset_id ,
59+ latest_build .job_id ,
60+ workspaces .created_at
61+ FROM workspaces
62+ JOIN LATERAL (
63+ SELECT
64+ workspace_builds .id ,
65+ workspace_builds .workspace_id ,
66+ workspace_builds .template_version_id ,
67+ workspace_builds .job_id ,
68+ workspace_builds .template_version_preset_id ,
69+ workspace_builds .transition ,
70+ workspace_builds .created_at ,
71+ provisioner_jobs .job_status
72+ FROM workspace_builds
73+ JOIN provisioner_jobsON provisioner_jobs .id = workspace_builds .job_id
74+ WHERE workspace_builds .workspace_id = workspaces .id
75+ AND workspace_builds .transition = ' start'
76+ AND provisioner_jobs .job_status = ' succeeded'
77+ ORDER BY workspace_builds .build_number DESC
78+ LIMIT 1
79+ )AS latest_buildON true
80+ WHERE workspaces .deleted = false
81+ AND workspaces .owner_id = ' c42fdf75-3097-471c-8c33-fb52454d81c0'
82+ ),
83+ ready_agentsAS (
84+ SELECT
85+ latest_prebuilds .job_id ,
86+ BOOL_AND(workspace_agents .lifecycle_state = ' ready' boolean AS ready
87+ FROM latest_prebuilds
88+ JOIN workspace_resourcesON workspace_resources .job_id = latest_prebuilds .job_id
89+ JOIN workspace_agentsON workspace_agents .resource_id = workspace_resources .id
90+ WHERE workspace_agents .deleted = false
91+ AND workspace_agents .parent_id ISNULL
92+ GROUP BY latest_prebuilds .job_id
93+ )
5294SELECT
53- p .id ,
54- p .name ,
55- p .template_id ,
56- b .template_version_id ,
57- p .current_preset_id AS current_preset_id,
58- p .ready ,
59- p .created_at
60- FROM workspace_prebuilds p
61- INNER JOIN workspace_latest_builds bON b .workspace_id = p .id
62- WHERE (b .transition = ' start'
63- AND b .job_status = ' succeeded'
95+ latest_prebuilds .workspace_id AS id,
96+ latest_prebuilds .name ,
97+ latest_prebuilds .template_id ,
98+ latest_prebuilds .template_version_id ,
99+ latest_prebuilds .template_version_preset_id AS current_preset_id,
100+ COALESCE(ready_agents .ready , false)::boolean AS ready,
101+ latest_prebuilds .created_at
102+ FROM latest_prebuilds
103+ LEFT JOIN ready_agentsON ready_agents .job_id = latest_prebuilds .job_id
104+ ORDER BY latest_prebuilds .workspace_id ASC ;
64105
65106-- name: CountInProgressPrebuilds :many
66107-- CountInProgressPrebuilds returns the number of in-progress prebuilds, grouped by preset ID and transition.