From c8d7bbbfec80852c896fe8a923bf9662bc4fc922 Mon Sep 17 00:00:00 2001 From: Michael Ritter Date: Tue, 12 Dec 2023 18:21:52 -0500 Subject: [PATCH] DRYD-1328: Use distinct in order to get media for all rows (#378) --- .../src/main/resources/tombstone_with_budget.jrxml | 5 ++--- .../src/main/resources/tombstone_with_creator.jrxml | 5 ++--- 2 files changed, 4 insertions(+), 6 deletions(-) diff --git a/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_budget.jrxml b/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_budget.jrxml index 405eb4b95..dffa35807 100644 --- a/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_budget.jrxml +++ b/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_budget.jrxml @@ -151,14 +151,13 @@ LEFT JOIN materials material on material.objcsid = obj.objcsid LEFT JOIN dimensions dimension on dimension.objcsid = obj.objcsid LEFT JOIN dates date on date.objcsid = obj.objcsid LEFT JOIN ( - SELECT relation.subjectcsid, relation.objectcsid + SELECT DISTINCT ON (relation.subjectcsid) relation.subjectcsid, relation.objectcsid FROM relations_common relation INNER JOIN misc ON misc.id = relation.id AND misc.lifecyclestate != 'deleted' INNER JOIN hierarchy hier ON hier.name = relation.objectcsid INNER JOIN collectionspace_core core ON core.id = hier.id WHERE relation.objectdocumenttype = 'Media' AND relation.subjectdocumenttype = 'CollectionObject' - ORDER BY core.updatedat DESC - LIMIT 1 + ORDER BY relation.subjectcsid, core.updatedat DESC ) media ON media.subjectcsid = obj.objcsid LEFT JOIN ( SELECT diff --git a/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_creator.jrxml b/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_creator.jrxml index 909c8f9cd..0d478f605 100644 --- a/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_creator.jrxml +++ b/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_creator.jrxml @@ -101,14 +101,13 @@ LEFT JOIN ( GROUP BY material_hier.parentid ) material ON material.parentid = obj.id LEFT JOIN ( - SELECT relation.subjectcsid, relation.objectcsid + SELECT DISTINCT ON (relation.subjectcsid) relation.subjectcsid, relation.objectcsid FROM relations_common relation INNER JOIN misc ON misc.id = relation.id AND misc.lifecyclestate != 'deleted' INNER JOIN hierarchy hier ON hier.name = relation.objectcsid INNER JOIN collectionspace_core core ON core.id = hier.id WHERE relation.objectdocumenttype = 'Media' AND relation.subjectdocumenttype = 'CollectionObject' - ORDER BY core.updatedat DESC - LIMIT 1 + ORDER BY relation.subjectcsid, core.updatedat DESC ) media ON media.subjectcsid = hier.name $P!{whereclause}]]> -- 2.47.3