Skip to content

Add sorting by `component_name` to dependency list aggregations

Michael Becker requested to merge wandering_person/add-sorting-by-component-name into master

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

Merge request reports