Only show blocking builds which actually block at least one build

Otherwise this turns in to a list of mostly canceled builds.

1 files changed, 57 insertions, 53 deletions

diff --git a/guix-build-coordinator/datastore/sqlite.scm b/guix-build-coordinator/datastore/sqlite.scm
index e374bbc..c60d5d5 100644
--- a/guix-build-coordinator/datastore/sqlite.scm
+++ b/guix-build-coordinator/datastore/sqlite.scm
@@ -738,61 +738,65 @@ WHERE related_derivations.name != :derivation
              db
              (string-append
               "
-SELECT
-  builds.uuid,
-  builds.derivation_name,
-  (
-    WITH RECURSIVE related_derivations(name) AS (
-      VALUES(builds.derivation_name)
-    UNION
-      SELECT derivation_inputs.derivation_name
-      FROM derivation_outputs
-      INNER JOIN related_derivations
-        ON derivation_outputs.derivation_name = related_derivations.name
-      INNER JOIN derivation_inputs
-        ON derivation_outputs.id = derivation_inputs.derivation_output_id
-    )
-    SELECT COUNT(DISTINCT blocked_builds.uuid)
-    FROM related_derivations
-    INNER JOIN builds AS blocked_builds
-      ON related_derivations.name = blocked_builds.derivation_name
-    AND blocked_builds.processed = 0
-    AND blocked_builds.canceled = 0
-  )
-FROM builds"
-              (if system
-                  "
-INNER JOIN derivations ON derivations.name = builds.derivation_name"
-                  "")
-              "
-LEFT JOIN build_results
-  ON builds.uuid = build_results.build_id
-WHERE
-  (
+SELECT *
+FROM (
+  SELECT
+    builds.uuid,
+    builds.derivation_name,
+    (
+      WITH RECURSIVE related_derivations(name) AS (
+        VALUES(builds.derivation_name)
+      UNION
+        SELECT derivation_inputs.derivation_name
+        FROM derivation_outputs
+        INNER JOIN related_derivations
+          ON derivation_outputs.derivation_name = related_derivations.name
+        INNER JOIN derivation_inputs
+          ON derivation_outputs.id = derivation_inputs.derivation_output_id
+      )
+      SELECT COUNT(DISTINCT blocked_builds.uuid)
+      FROM related_derivations
+      INNER JOIN builds AS blocked_builds
+        ON related_derivations.name = blocked_builds.derivation_name
+      AND blocked_builds.processed = 0
+      AND blocked_builds.canceled = 0
+    ) AS blocking_count
+  FROM builds"
+                (if system
+                    "
+  INNER JOIN derivations ON derivations.name = builds.derivation_name"
+                    "")
+                "
+  LEFT JOIN build_results
+    ON builds.uuid = build_results.build_id
+  WHERE
     (
-      builds.processed = 1
-      AND build_results.result = 'failure'
-    ) OR (
-      builds.canceled = 1
+      (
+        builds.processed = 1
+        AND build_results.result = 'failure'
+      ) OR (
+        builds.canceled = 1
+      )
+    )"
+                (if system
+                    "
+    AND derivations.system = :system"
+                    "")
+                "
+    AND NOT EXISTS (
+      SELECT 1
+      FROM derivation_outputs
+      INNER JOIN derivation_outputs AS other_build_derivation_outputs
+        ON derivation_outputs.output = other_build_derivation_outputs.output
+      INNER JOIN builds AS other_builds
+        ON other_build_derivation_outputs.derivation_name = other_builds.derivation_name
+      INNER JOIN build_results AS other_build_results
+        ON other_builds.uuid = other_build_results.build_id
+      WHERE derivation_outputs.derivation_name = builds.derivation_name
+        AND other_build_results.result = 'success'
     )
-  )"
-              (if system
-                  "
-  AND derivations.system = :system"
-                  "")
-              "
-  AND NOT EXISTS (
-    SELECT 1
-    FROM derivation_outputs
-    INNER JOIN derivation_outputs AS other_build_derivation_outputs
-      ON derivation_outputs.output = other_build_derivation_outputs.output
-    INNER JOIN builds AS other_builds
-      ON other_build_derivation_outputs.derivation_name = other_builds.derivation_name
-    INNER JOIN build_results AS other_build_results
-      ON other_builds.uuid = other_build_results.build_id
-    WHERE derivation_outputs.derivation_name = builds.derivation_name
-      AND other_build_results.result = 'success'
-  )
+) AS data
+WHERE blocking_count > 0
 ORDER BY 3 DESC, 2, 1")
              #:cache? #t)))