`AGGREGATE_LATEST_VERSION_CHILDREN` in deeper hirachies

[Xenira]

Current Behavior

We have projects nested multiple levels deep and want to have the collection projects use AGGREGATE_LATEST_VERSION_CHILDREN.
This works for the deepest level, but all levels above that ignore the contained collection projects as they can not be marked as latest.

In the image i would expect the top level project to have a sum of all non collection projects, as they are all marked as latest.

Steps to Reproduce

1. Create collection projects multiple levels deep with collection logic AGGREGATE_LATEST_VERSION_CHILDREN
2. Add a project with vulnerabilities to the lowest level

Expected Behavior

I would expect collection projects to be counted as well.
Not sure if it makes sense to limit to only collection projects with AGGREGATE_LATEST_VERSION_CHILDREN.

I can have a try at implementing this.

Dependency-Track Version

5.x

Browser

Other

Checklist

• I have read and understand the contributing guidelines
• I have checked the existing issues for whether this defect was already reported

[nscuro]

Yeah I think this should "just work" once collection projects can be marked as latest. The backing query is already recursive and takes the collection logic of each level into consideration:

dependency-track/apiserver/src/main/java/org/dependencytrack/persistence/jdbi/MetricsDao.java

Lines 436 to 532 in f1fb4e9

	WITH RECURSIVE
	collection_descendants AS(
	SELECT parent."ID" AS root_id
	, child."ID" AS project_id
	, child."COLLECTION_LOGIC"
	, child."COLLECTION_TAG_ID"
	FROM UNNEST(:projectIds) AS input(id)
	INNER JOIN "PROJECT" parent
	ON parent."ID" = input.id
	INNER JOIN "PROJECT_HIERARCHY" ph
	ON ph."PARENT_PROJECT_ID" = parent."ID"
	AND ph."DEPTH" = 1
	INNER JOIN "PROJECT" child
	ON child."ID" = ph."CHILD_PROJECT_ID"
	AND child."INACTIVE_SINCE" IS NULL
	AND (
	parent."COLLECTION_LOGIC" != 'AGGREGATE_DIRECT_CHILDREN_WITH_TAG'
	OR EXISTS (
	SELECT 1
	FROM "PROJECTS_TAGS" pt
	WHERE pt."PROJECT_ID" = child."ID"
	AND pt."TAG_ID" = parent."COLLECTION_TAG_ID"
	)
	)
	AND (
	parent."COLLECTION_LOGIC" != 'AGGREGATE_LATEST_VERSION_CHILDREN'
	OR child."IS_LATEST"
	)
	UNION ALL
	SELECT cd.root_id
	, child."ID"
	, child."COLLECTION_LOGIC"
	, child."COLLECTION_TAG_ID"
	FROM collection_descendants cd
	INNER JOIN "PROJECT_HIERARCHY" ph
	ON ph."PARENT_PROJECT_ID" = cd.project_id
	AND ph."DEPTH" = 1
	INNER JOIN "PROJECT" child
	ON child."ID" = ph."CHILD_PROJECT_ID"
	WHERE cd."COLLECTION_LOGIC" IS NOT NULL
	AND child."INACTIVE_SINCE" IS NULL
	AND (
	cd."COLLECTION_LOGIC" != 'AGGREGATE_DIRECT_CHILDREN_WITH_TAG'
	OR EXISTS (
	SELECT 1
	FROM "PROJECTS_TAGS" pt
	WHERE pt."PROJECT_ID" = child."ID"
	AND pt."TAG_ID" = cd."COLLECTION_TAG_ID"
	)
	)
	AND (
	cd."COLLECTION_LOGIC" != 'AGGREGATE_LATEST_VERSION_CHILDREN'
	OR child."IS_LATEST"
	)
	)
	CYCLE project_id SET is_cycle USING path
	SELECT cd.root_id AS "projectId"
	, COALESCE(SUM(pm."COMPONENTS"), 0) AS components
	, COALESCE(SUM(pm."CRITICAL"), 0) AS critical
	, COALESCE(SUM(pm."HIGH"), 0) AS high
	, COALESCE(SUM(pm."LOW"), 0) AS low
	, COALESCE(SUM(pm."MEDIUM"), 0) AS medium
	, COALESCE(SUM(pm."UNASSIGNED_SEVERITY"), 0) AS unassigned
	, COALESCE(SUM(pm."VULNERABILITIES"), 0) AS vulnerabilities
	, COALESCE(SUM(pm."VULNERABLECOMPONENTS"), 0) AS "vulnerableComponents"
	, COALESCE(SUM(pm."FINDINGS_TOTAL"), 0) AS "findingsTotal"
	, COALESCE(SUM(pm."FINDINGS_AUDITED"), 0) AS "findingsAudited"
	, COALESCE(SUM(pm."FINDINGS_UNAUDITED"), 0) AS "findingsUnaudited"
	, COALESCE(SUM(pm."SUPPRESSED"), 0) AS suppressed
	, COALESCE(SUM(pm."RISKSCORE"), 0) AS "inheritedRiskScore"
	, COALESCE(SUM(pm."POLICYVIOLATIONS_TOTAL"), 0) AS "policyViolationsTotal"
	, COALESCE(SUM(pm."POLICYVIOLATIONS_FAIL"), 0) AS "policyViolationsFail"
	, COALESCE(SUM(pm."POLICYVIOLATIONS_WARN"), 0) AS "policyViolationsWarn"
	, COALESCE(SUM(pm."POLICYVIOLATIONS_INFO"), 0) AS "policyViolationsInfo"
	, COALESCE(SUM(pm."POLICYVIOLATIONS_AUDITED"), 0) AS "policyViolationsAudited"
	, COALESCE(SUM(pm."POLICYVIOLATIONS_UNAUDITED"), 0) AS "policyViolationsUnaudited"
	, COALESCE(SUM(pm."POLICYVIOLATIONS_SECURITY_TOTAL"), 0) AS "policyViolationsSecurityTotal"
	, COALESCE(SUM(pm."POLICYVIOLATIONS_SECURITY_AUDITED"), 0) AS "policyViolationsSecurityAudited"
	, COALESCE(SUM(pm."POLICYVIOLATIONS_SECURITY_UNAUDITED"), 0) AS "policyViolationsSecurityUnaudited"
	, COALESCE(SUM(pm."POLICYVIOLATIONS_LICENSE_TOTAL"), 0) AS "policyViolationsLicenseTotal"
	, COALESCE(SUM(pm."POLICYVIOLATIONS_LICENSE_AUDITED"), 0) AS "policyViolationsLicenseAudited"
	, COALESCE(SUM(pm."POLICYVIOLATIONS_LICENSE_UNAUDITED"), 0) AS "policyViolationsLicenseUnaudited"
	, COALESCE(SUM(pm."POLICYVIOLATIONS_OPERATIONAL_TOTAL"), 0) AS "policyViolationsOperationalTotal"
	, COALESCE(SUM(pm."POLICYVIOLATIONS_OPERATIONAL_AUDITED"), 0) AS "policyViolationsOperationalAudited"
	, COALESCE(SUM(pm."POLICYVIOLATIONS_OPERATIONAL_UNAUDITED"), 0) AS "policyViolationsOperationalUnaudited"
	, MIN(pm."FIRST_OCCURRENCE") AS "firstOccurrence"
	, MAX(pm."LAST_OCCURRENCE") AS "lastOccurrence"
	FROM collection_descendants cd
	LEFT JOIN LATERAL (
	SELECT *
	FROM "PROJECTMETRICS"
	WHERE "PROJECT_ID" = cd.project_id
	ORDER BY "LAST_OCCURRENCE" DESC
	LIMIT 1
	) pm ON TRUE
	WHERE cd."COLLECTION_LOGIC" IS NULL
	GROUP BY cd.root_id

[Xenira]

Is there an issue for allowing that, didn't find one?
If not should I create a separate one or just do it in the context of this one?

[nscuro]

I think this issue suffices.

[Xenira]

Collection projects actually can be marked as latest. I thought it wasnt possible as the isLatest switch was disabled because the version was empty:
https://github.com/DependencyTrack/frontend/blob/ad24ecd05a4cdb72e694c782deef05cf7c75c6a9/src/views/portfolio/projects/ProjectDetailsModal.vue#L77

When deleting that line i can set latest and the aggregate works

Not sure if it should be able to set empty versions as latest. For collection projects it makes sense I guess but should it stay disabled for non collection projects?

EDIT: Tested the api and creating latest version collection projects works as expected.