Remove `COUNT(*)` query

What does this MR do and why?

This change removes two queries from each invocation to GET /<group>/-/dependencies.json.

1. COUNT(*) query that is used for displaying the pagination links.
2. The equivalent of an EXISTS query to compute the report status in the JSON response. i.e. { report: { status: ok }, dependencies: [] }.

The following queries have been removed:

  Sbom::Occurrence Count (2.2ms)  SELECT COUNT(*) FROM (WITH "our_occurrences" AS MATERIALIZED (SELECT "sbom_occurrences".* FROM "sbom_occurrences" WHERE "sbom_occurrences"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."namespace_id" IN (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{71}'))) AND "projects"."marked_for_deletion_at" IS NULL AND "projects"."pending_delete" = FALSE)) SELECT 1 AS one FROM "sbom_occurrences" INNER JOIN (
            SELECT component_id,
                  COUNT(DISTINCT id) AS occurrence_count,
                  COUNT(DISTINCT project_id) AS project_count
            FROM our_occurrences
            GROUP BY component_id
          ) agg_occurrences ON sbom_occurrences.component_id = agg_occurrences.component_id WHERE "sbom_occurrences"."id" IN (SELECT "our_occurrences"."id" FROM "our_occurrences") LIMIT 10001) subquery_for_count /*application:web,correlation_id:01HBV8P7KXCA942CQPV23TYCDH,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/lib/gitlab/pagination/offset_pagination.rb:84:in `exceeeds_count?'*/
  ↳ config/initializers/kaminari_active_record_relation_methods_with_limit.rb:31:in `total_count_with_limit'

SELECT COUNT(*)
FROM (
  WITH "our_occurrences" AS MATERIALIZED (
    SELECT "sbom_occurrences".*
    FROM "sbom_occurrences"
    WHERE "sbom_occurrences"."project_id" IN (
      SELECT "projects"."id"
      FROM "projects"
      WHERE "projects"."namespace_id" IN (
        SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
        FROM "namespaces"
        WHERE "namespaces"."type" = 'Group'
        AND (traversal_ids @> ('{9970}'))
      )
      AND "projects"."marked_for_deletion_at" IS NULL
      AND "projects"."pending_delete" = FALSE
    )
  ) SELECT 1 AS one FROM "sbom_occurrences" INNER JOIN (
    SELECT component_id,
          COUNT(DISTINCT id) AS occurrence_count,
          COUNT(DISTINCT project_id) AS project_count
    FROM our_occurrences
    GROUP BY component_id
  ) agg_occurrences ON sbom_occurrences.component_id = agg_occurrences.component_id
  WHERE "sbom_occurrences"."id" IN (
    SELECT "our_occurrences"."id" FROM "our_occurrences"
  )
  LIMIT 10001
)

The following query is still a problem.

WITH "our_occurrences" AS MATERIALIZED (SELECT "sbom_occurrences".* FROM "sbom_occurrences" WHERE "sbom_occurrences"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."namespace_id" IN (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{1130}'))) AND "projects"."marked_for_deletion_at" IS NULL AND "projects"."pending_delete" = FALSE)) SELECT 1 AS one FROM "sbom_occurrences" INNER JOIN (
            SELECT component_id,
                  COUNT(DISTINCT id) AS occurrence_count,
                  COUNT(DISTINCT project_id) AS project_count
            FROM our_occurrences
            GROUP BY component_id
          ) agg_occurrences ON sbom_occurrences.component_id = agg_occurrences.component_id WHERE "sbom_occurrences"."id" IN (SELECT "our_occurrences"."id" FROM "our_occurrences") /* allow_cross_joins_across_databases */ LIMIT 1 OFFSET 0 /*application:test,correlation_id:c81efeec-8167-445a-b12a-c39efac7c95c,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/serializers/report_list_entity.rb:40:in `status'*/

WITH "our_occurrences" AS MATERIALIZED (
  SELECT "sbom_occurrences".*
  FROM "sbom_occurrences"
  WHERE "sbom_occurrences"."project_id" IN (
    SELECT "projects"."id"
    FROM "projects"
    WHERE "projects"."namespace_id" IN (
      SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
      FROM "namespaces"
      WHERE "namespaces"."type" = 'Group'
      AND (traversal_ids @> ('{9970}'))
    )
    AND "projects"."marked_for_deletion_at" IS NULL
    AND "projects"."pending_delete" = FALSE
  )
)
SELECT 1 AS one
FROM "sbom_occurrences" INNER JOIN (
  SELECT component_id,
        COUNT(DISTINCT id) AS occurrence_count,
        COUNT(DISTINCT project_id) AS project_count
  FROM our_occurrences
  GROUP BY component_id
) agg_occurrences ON sbom_occurrences.component_id = agg_occurrences.component_id
WHERE "sbom_occurrences"."id" IN (
  SELECT "our_occurrences"."id"
  FROM "our_occurrences"
)
LIMIT 1 OFFSET 0;

#425274 (comment 1577534413)

Screenshots or screen recordings

Before	After

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.