Add sorting by `component_name` to dependency list aggregations
What does this MR do and why?
This mirrors the work done in MR 154054, which added sorting based on highest_severity
For more information about AggregationsFinder
, please see !150476 (merged).
related to: #450920 (closed)
Database
Ascending order
SQL
WITH "namespaces" AS MATERIALIZED (
SELECT
"namespaces"."traversal_ids"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids >= (
'{9970}'
)
)
AND (
traversal_ids < (
'{9971}'
)
))
SELECT
MIN(outer_occurrences.id)::bigint AS id,
"outer_occurrences"."component_id",
"outer_occurrences"."component_version_id",
MIN(outer_occurrences.package_manager) AS package_manager,
MIN(outer_occurrences.input_file_path) AS input_file_path,
JSONB_AGG(outer_occurrences.licenses -> 0) AS licenses,
SUM(counts.occurrence_count)::integer AS occurrence_count,
SUM(counts.vulnerability_count)::integer AS vulnerability_count,
SUM(counts.project_count)::integer AS project_count
FROM ( SELECT DISTINCT ON ("inner_occurrences"."component_name", "inner_occurrences"."component_id", "inner_occurrences"."component_version_id")
"inner_occurrences"."id",
"inner_occurrences"."created_at",
"inner_occurrences"."updated_at",
"inner_occurrences"."component_version_id",
"inner_occurrences"."project_id",
"inner_occurrences"."pipeline_id",
"inner_occurrences"."source_id",
"inner_occurrences"."commit_sha",
"inner_occurrences"."component_id",
"inner_occurrences"."uuid",
"inner_occurrences"."package_manager",
"inner_occurrences"."component_name",
"inner_occurrences"."input_file_path",
"inner_occurrences"."licenses",
"inner_occurrences"."highest_severity",
"inner_occurrences"."vulnerability_count",
"inner_occurrences"."source_package_id",
"inner_occurrences"."archived",
"inner_occurrences"."traversal_ids",
"inner_occurrences"."ancestors"
FROM
namespaces,
LATERAL ( SELECT DISTINCT ON ("sbom_occurrences"."component_name", "sbom_occurrences"."component_id", "sbom_occurrences"."component_version_id")
"sbom_occurrences"."id",
"sbom_occurrences"."created_at",
"sbom_occurrences"."updated_at",
"sbom_occurrences"."component_version_id",
"sbom_occurrences"."project_id",
"sbom_occurrences"."pipeline_id",
"sbom_occurrences"."source_id",
"sbom_occurrences"."commit_sha",
"sbom_occurrences"."component_id",
"sbom_occurrences"."uuid",
"sbom_occurrences"."package_manager",
"sbom_occurrences"."component_name",
"sbom_occurrences"."input_file_path",
"sbom_occurrences"."licenses",
"sbom_occurrences"."highest_severity",
"sbom_occurrences"."vulnerability_count",
"sbom_occurrences"."source_package_id",
"sbom_occurrences"."archived",
"sbom_occurrences"."traversal_ids",
"sbom_occurrences"."ancestors"
FROM
"sbom_occurrences"
WHERE (sbom_occurrences.traversal_ids = namespaces.traversal_ids::bigint[])
AND "sbom_occurrences"."archived" = FALSE
ORDER BY
"sbom_occurrences"."component_name" ASC,
"sbom_occurrences"."component_id" ASC,
"sbom_occurrences"."component_version_id" ASC
LIMIT 20) inner_occurrences
ORDER BY
inner_occurrences.component_name ASC,
inner_occurrences.component_id ASC,
inner_occurrences.component_version_id ASC
LIMIT 20) outer_occurrences,
LATERAL (
SELECT
COUNT(project_id) AS occurrence_count,
COUNT(DISTINCT project_id) project_count,
SUM(vulnerability_count) vulnerability_count
FROM
"sbom_occurrences"
WHERE (traversal_ids >= ('{9970}'))
AND (traversal_ids < ('{9971}'))
AND "sbom_occurrences"."archived" = FALSE
AND (sbom_occurrences.component_version_id = outer_occurrences.component_version_id)) counts
GROUP BY
"outer_occurrences"."component_name",
"outer_occurrences"."component_id",
"outer_occurrences"."component_version_id"
ORDER BY
MIN(outer_occurrences.component_name) ASC,
MIN(outer_occurrences.component_id) ASC,
MIN(outer_occurrences.component_version_id) ASC
Descending order
SQL
WITH "namespaces" AS MATERIALIZED (
SELECT
"namespaces"."traversal_ids"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (
traversal_ids >= (
'{9970}'
)
)
AND (
traversal_ids < (
'{9971}'
)
))
SELECT
MIN(outer_occurrences.id)::bigint AS id,
"outer_occurrences"."component_id",
"outer_occurrences"."component_version_id",
MIN(outer_occurrences.package_manager) AS package_manager,
MIN(outer_occurrences.input_file_path) AS input_file_path,
JSONB_AGG(outer_occurrences.licenses -> 0) AS licenses,
SUM(counts.occurrence_count)::integer AS occurrence_count,
SUM(counts.vulnerability_count)::integer AS vulnerability_count,
SUM(counts.project_count)::integer AS project_count
FROM ( SELECT DISTINCT ON ("inner_occurrences"."component_name", "inner_occurrences"."component_id", "inner_occurrences"."component_version_id")
"inner_occurrences"."id",
"inner_occurrences"."created_at",
"inner_occurrences"."updated_at",
"inner_occurrences"."component_version_id",
"inner_occurrences"."project_id",
"inner_occurrences"."pipeline_id",
"inner_occurrences"."source_id",
"inner_occurrences"."commit_sha",
"inner_occurrences"."component_id",
"inner_occurrences"."uuid",
"inner_occurrences"."package_manager",
"inner_occurrences"."component_name",
"inner_occurrences"."input_file_path",
"inner_occurrences"."licenses",
"inner_occurrences"."highest_severity",
"inner_occurrences"."vulnerability_count",
"inner_occurrences"."source_package_id",
"inner_occurrences"."archived",
"inner_occurrences"."traversal_ids",
"inner_occurrences"."ancestors"
FROM
namespaces,
LATERAL ( SELECT DISTINCT ON ("sbom_occurrences"."component_name", "sbom_occurrences"."component_id", "sbom_occurrences"."component_version_id")
"sbom_occurrences"."id",
"sbom_occurrences"."created_at",
"sbom_occurrences"."updated_at",
"sbom_occurrences"."component_version_id",
"sbom_occurrences"."project_id",
"sbom_occurrences"."pipeline_id",
"sbom_occurrences"."source_id",
"sbom_occurrences"."commit_sha",
"sbom_occurrences"."component_id",
"sbom_occurrences"."uuid",
"sbom_occurrences"."package_manager",
"sbom_occurrences"."component_name",
"sbom_occurrences"."input_file_path",
"sbom_occurrences"."licenses",
"sbom_occurrences"."highest_severity",
"sbom_occurrences"."vulnerability_count",
"sbom_occurrences"."source_package_id",
"sbom_occurrences"."archived",
"sbom_occurrences"."traversal_ids",
"sbom_occurrences"."ancestors"
FROM
"sbom_occurrences"
WHERE (sbom_occurrences.traversal_ids = namespaces.traversal_ids::bigint[])
AND "sbom_occurrences"."archived" = FALSE
ORDER BY
"sbom_occurrences"."component_name" DESC,
"sbom_occurrences"."component_id" DESC,
"sbom_occurrences"."component_version_id" DESC
LIMIT 20) inner_occurrences
ORDER BY
inner_occurrences.component_name DESC,
inner_occurrences.component_id DESC,
inner_occurrences.component_version_id DESC
LIMIT 20) outer_occurrences,
LATERAL (
SELECT
COUNT(project_id) AS occurrence_count,
COUNT(DISTINCT project_id) project_count,
SUM(vulnerability_count) vulnerability_count
FROM
"sbom_occurrences"
WHERE (traversal_ids >= ('{9970}'))
AND (traversal_ids < ('{9971}'))
AND "sbom_occurrences"."archived" = FALSE
AND (sbom_occurrences.component_version_id = outer_occurrences.component_version_id)) counts
GROUP BY
"outer_occurrences"."component_name",
"outer_occurrences"."component_id",
"outer_occurrences"."component_version_id"
ORDER BY
MIN(outer_occurrences.component_name) DESC,
MIN(outer_occurrences.component_id) DESC,
MIN(outer_occurrences.component_version_id) DESC
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Changelog: added
Edited by Michael Becker