New query for group-level dependency list page sorting by `severity`
Why are we doing this work
We need to implement this SQL query to retrieve records performantly.
WITH namespaces AS MATERIALIZED (
SELECT
traversal_ids::bigint[]
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group' AND (traversal_ids @> ('{9970}'))
)
SELECT
occurrences.component_version_id,
SUM(occurrence_count) AS occurrence_count,
SUM(project_count) AS project_count
FROM
(SELECT
DISTINCT ON (component_name, component_version_id)
occurrences.id,
occurrences.component_version_id,
occurrences.component_name
FROM
namespaces,
LATERAL (
SELECT
DISTINCT ON (component_name, component_version_id)
sbom_occurrences.id,
sbom_occurrences.component_version_id,
sbom_occurrences.component_name
FROM
sbom_occurrences
WHERE
sbom_occurrences.archived IS FALSE AND
sbom_occurrences.traversal_ids = namespaces.traversal_ids
ORDER BY sbom_occurrences.component_name ASC, sbom_occurrences.component_version_id ASC
LIMIT 25
) occurrences
ORDER BY occurrences.component_name ASC, occurrences.component_version_id ASC
LIMIT 25) AS occurrences,
LATERAL (
SELECT
COUNT(project_id) AS occurrence_count,
COUNT(DISTINCT project_id) project_count
FROM
sbom_occurrences
WHERE
sbom_occurrences.archived IS FALSE AND
sbom_occurrences.component_version_id = occurrences.component_version_id AND
sbom_occurrences.traversal_ids >= '{9970}'::bigint[] AND
sbom_occurrences.traversal_ids < '{9971}'::bigint[]
) occurrences_agg
GROUP BY occurrences.component_name, occurrences.component_version_id
ORDER BY MIN(occurrences.component_name) ASC, MIN(occurrences.component_version_id) ASC
This query will perform keyset pagination.
Implementation plan
Verification steps
TBD