From 5be4ebca0c159d3029426c45d71f4f0b2b542a6c Mon Sep 17 00:00:00 2001 From: Christopher Baines Date: Sun, 24 Mar 2019 10:50:48 +0000 Subject: Improve the query in select-derivations-and-build-status Fix handling derivations which target multiple systems (somehow?). --- guix-data-service/model/derivation.scm | 35 +++++++++++++++++----------------- 1 file changed, 18 insertions(+), 17 deletions(-) (limited to 'guix-data-service/model/derivation.scm') diff --git a/guix-data-service/model/derivation.scm b/guix-data-service/model/derivation.scm index f7b2cab..2469d0e 100644 --- a/guix-data-service/model/derivation.scm +++ b/guix-data-service/model/derivation.scm @@ -499,23 +499,24 @@ ORDER BY derivations.system DESC, (define query (string-append - "SELECT derivations.file_name, derivations.system, (" - " SELECT DISTINCT package_derivations.target" - " FROM package_derivations" - " WHERE derivations.id = package_derivations.derivation_id" - ") AS target, " - "latest_build_status.status " - "FROM derivations " - "INNER JOIN package_derivations" - " ON derivations.id = package_derivations.derivation_id " - "LEFT OUTER JOIN builds ON derivations.id = builds.derivation_id " - "LEFT OUTER JOIN " - "(SELECT DISTINCT ON (internal_build_id) * " - "FROM build_status " - "ORDER BY internal_build_id, status_fetched_at DESC" - ") AS latest_build_status " - "ON builds.internal_id = latest_build_status.internal_build_id " - "WHERE " criteria ";")) + " +SELECT + derivations.file_name, + derivations.system, + package_derivations.target, + latest_build_status.status +FROM derivations +INNER JOIN package_derivations + ON derivations.id = package_derivations.derivation_id +LEFT OUTER JOIN builds + ON derivations.id = builds.derivation_id +LEFT OUTER JOIN ( + SELECT DISTINCT ON (internal_build_id) * + FROM build_status + ORDER BY internal_build_id, status_fetched_at DESC +) AS latest_build_status +ON builds.internal_id = latest_build_status.internal_build_id +WHERE " criteria ";")) (exec-query conn query)) -- cgit v1.2.3