Skip to content

Add sorting by `package_manager` to dependency list aggregations

Michael Becker requested to merge 437648-new-query-for-group-level-dependency-list-page-sorting-by-package_manager into master

What does this MR do and why?

This mirrors the work done in:

  • MR 154054 (added sorting based on highest_severity)
  • MR 154779 (added sorting based on component_name)

Since all of the specs for sort_by are very similar, I also refactored the existing specs into a shared_example

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"."package_manager", "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"."package_manager", "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"."package_manager" ASC,
            "sbom_occurrences"."component_id" ASC,
            "sbom_occurrences"."component_version_id" ASC
        LIMIT 20) inner_occurrences
ORDER BY
    inner_occurrences.package_manager 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"."package_manager",
    "outer_occurrences"."component_id",
    "outer_occurrences"."component_version_id"
ORDER BY
    MIN(outer_occurrences.package_manager) 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"."package_manager", "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"."package_manager", "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"."package_manager" DESC,
            "sbom_occurrences"."component_id" DESC,
            "sbom_occurrences"."component_version_id" DESC
        LIMIT 20) inner_occurrences
ORDER BY
    inner_occurrences.package_manager 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"."package_manager",
    "outer_occurrences"."component_id",
    "outer_occurrences"."component_version_id"
ORDER BY
    MIN(outer_occurrences.package_manager) DESC,
    MIN(outer_occurrences.component_id) DESC,
    MIN(outer_occurrences.component_version_id) DESC

Screenshots or screen recordings

Spec structure before Spec structure after
image image

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
related to: #437648 (closed)

Edited by Michael Becker

Merge request reports