@@ -220,31 +220,32 @@ SELECT
220220sqlc .embed (workspace_agents),
221221sqlc .embed (workspace_build_with_user)
222222FROM
223- -- Only get the latest build for each workspace
224- (
225- SELECT
226- workspace_id,MAX (build_number)as max_build_number
227- FROM
228- workspace_build_with_user
229- GROUP BY
230- workspace_id
231- )as latest_builds
232- -- Pull the workspace_build rows for returning
233- INNER JOIN workspace_build_with_user
234- ON workspace_build_with_user .workspace_id = latest_builds .workspace_id
235- AND workspace_build_with_user .build_number = latest_builds .max_build_number
236- -- For each latest build, grab the resources to relate to an agent
237- INNER JOIN workspace_resources
238- ON workspace_resources .job_id = workspace_build_with_user .job_id
239- -- Agent <-> Resource is 1:1
240- INNER JOIN workspace_agents
241- ON workspace_agents .resource_id = workspace_resources .id
242- -- We need the owner ID
243- INNER JOIN workspaces
244- ON workspace_build_with_user .workspace_id = workspaces .id
223+ workspace_agents
224+ JOIN
225+ workspace_resources
226+ ON
227+ workspace_agents .resource_id = workspace_resources .id
228+ JOIN
229+ workspace_build_with_user
230+ ON
231+ workspace_resources .job_id = workspace_build_with_user .job_id
232+ JOIN
233+ workspaces
234+ ON
235+ workspace_build_with_user .workspace_id = workspaces .id
245236WHERE
246- -- This should only match 1 agent, so 1 returned row or 0
247- workspace_agents .auth_token = @auth_token
248- AND
249- workspaces .deleted = FALSE
237+ -- This should only match 1 agent, so 1 returned row or 0.
238+ workspace_agents .auth_token = @auth_token::uuid
239+ AND workspaces .deleted = FALSE
240+ -- Filter out builds that are not the latest.
241+ AND workspace_build_with_user .build_number = (
242+ -- Select from workspace_builds as it's one less join compared
243+ -- to workspace_build_with_user.
244+ SELECT
245+ MAX (build_number)
246+ FROM
247+ workspace_builds
248+ WHERE
249+ workspace_id= workspace_build_with_user .workspace_id
250+ )
250251;