01/03: Change select-build procedures to avoid join issues

[Christopher Baines]

cbaines pushed a commit to branch master
in repository data-service.

commit 1e2fefa7cf796b6a74f729b27805fbf140592ca7
Author: Christopher Baines <mail@cbaines.net>
AuthorDate: Wed Jul 1 19:42:42 2020 +0100

    Change select-build procedures to avoid join issues
    
    The number of build_status rows coming back was being amplified by the INNER
    JOIN, using a subquery guards against this.
---
 guix-data-service/model/build.scm | 40 +++++++++++++++++++++------------------
 1 file changed, 22 insertions(+), 18 deletions(-)

diff --git a/guix-data-service/model/build.scm 
b/guix-data-service/model/build.scm
index 9e81611..b9ba241 100644
--- a/guix-data-service/model/build.scm
+++ b/guix-data-service/model/build.scm
@@ -227,18 +227,20 @@ ORDER BY latest_build_status.timestamp DESC")
     "
 SELECT build_servers.url,
        builds.derivation_file_name,
-       JSON_AGG(
-         json_build_object(
-           'timestamp', build_status.timestamp,
-           'status', build_status.status
-         )
-         ORDER BY build_status.timestamp
+       (
+         SELECT JSON_AGG(
+                  json_build_object(
+                    'timestamp', build_status.timestamp,
+                    'status', build_status.status
+                  )
+                  ORDER BY build_status.timestamp
+                )
+         FROM build_status
+         WHERE build_status.build_id = builds.id
        ) AS statuses
 FROM builds
 INNER JOIN build_servers
   ON build_servers.id = builds.build_server_id
-INNER JOIN build_status
-  ON builds.id = build_status.build_id
 INNER JOIN derivations_by_output_details_set
   ON builds.derivation_output_details_set_id =
      derivations_by_output_details_set.derivation_output_details_set_id
@@ -246,7 +248,7 @@ INNER JOIN derivations
   ON derivations.id = derivations_by_output_details_set.derivation_id
 WHERE build_server_id = $1 AND
       builds.build_server_build_id = $2
-GROUP BY build_servers.url, builds.derivation_file_name")
+GROUP BY builds.id, build_servers.url, builds.derivation_file_name")
 
   (match (exec-query conn
                      query
@@ -265,18 +267,20 @@ GROUP BY build_servers.url, builds.derivation_file_name")
     "
 SELECT build_servers.url,
        builds.derivation_file_name,
-       JSON_AGG(
-         json_build_object(
-           'timestamp', build_status.timestamp,
-           'status', build_status.status
-         )
-         ORDER BY build_status.timestamp
+       (
+         SELECT JSON_AGG(
+                  json_build_object(
+                    'timestamp', build_status.timestamp,
+                    'status', build_status.status
+                  )
+                  ORDER BY build_status.timestamp
+                )
+         FROM build_status
+         WHERE build_status.build_id = builds.id
        ) AS statuses
 FROM builds
 INNER JOIN build_servers
   ON build_servers.id = builds.build_server_id
-INNER JOIN build_status
-  ON builds.id = build_status.build_id
 INNER JOIN derivations_by_output_details_set
   ON builds.derivation_output_details_set_id =
      derivations_by_output_details_set.derivation_output_details_set_id
@@ -284,7 +288,7 @@ INNER JOIN derivations
   ON derivations.id = derivations_by_output_details_set.derivation_id
 WHERE build_server_id = $1 AND
       derivations.file_name = $2
-GROUP BY build_servers.url, builds.derivation_file_name")
+GROUP BY builds.id, build_servers.url, builds.derivation_file_name")
 
   (match (exec-query conn
                      query