Group level drop down API implementation

What does this MR do and why?

Drop down API for the group level vulnerability reports that will be used by the frontend.

Similar to the project level API implemented in !171464 (merged)

References

• Spike: Identify solutions to support group leve... (#506480 - closed) • Bala Kumar • 17.7 • Needs attention

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.

Screenshots or screen recordings

How to set up and validate locally

1. Create a group and import project https://gitlab.com/gitlab-org/govern/threat-insights-demos/verification-projects/verify-owasp-top-10-grouping under the group. Run pipeline on the main branch to populate the vulnerabilities for the group.
2. In rails console enable feature flag for the group Feature.enable(:vulnerability_filtering_by_identifier_group, group).
3. If the group has less than 20k vulnerabilities, it displays the matching identifier names for the below GraphQL equivalent query like the attached screenshot.

{
  group(fullPath: "bala-test-group") {
    vulnerabilityIdentifierSearch(name: "injection")
  }
}

SQL

SELECT DISTINCT
    "vulnerability_identifiers"."name"
FROM
    "vulnerability_identifiers"
WHERE
    "vulnerability_identifiers"."project_id" IN (
        SELECT
            "projects"."id"
        FROM
            UNNEST(COALESCE((
                    SELECT
                        ids
                    FROM (
                        SELECT
                            "namespace_descendants"."all_project_ids" AS ids
                        FROM "namespace_descendants"
                    WHERE
                        "namespace_descendants"."outdated_at" IS NULL
                        AND "namespace_descendants"."namespace_id" = 9970) cached_query), (
                    SELECT
                        ids
                    FROM (
                        SELECT
                            ARRAY_AGG("projects"."id") AS ids
                        FROM (
                            SELECT
                                "projects"."id"
                            FROM "projects"
                        WHERE
                            "projects"."namespace_id" IN (
                                SELECT
                                    "namespaces"."id"
                                FROM UNNEST(COALESCE((
                                        SELECT
                                            ids
                                        FROM (
                                            SELECT
                                                "namespace_descendants"."self_and_descendant_group_ids" AS ids
                                            FROM "namespace_descendants"
                                        WHERE
                                            "namespace_descendants"."outdated_at" IS NULL
                                            AND "namespace_descendants"."namespace_id" = 9970) cached_query), (
                                        SELECT
                                            ids
                                        FROM (
                                            SELECT
                                                ARRAY_AGG("namespaces"."id") AS ids
                                            FROM (
                                                SELECT
                                                    namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
                                                FROM "namespaces"
                                            WHERE
                                                "namespaces"."type" = 'Group'
                                                AND (traversal_ids @> ('{9970}'))) namespaces) consistent_query))) AS namespaces (id))) projects) consistent_query))) AS projects (id))
    AND (name ILIKE '%cwe%')
ORDER BY
    "vulnerability_identifiers"."name" ASC
LIMIT 50
/*application:test,correlation_id:5a047764168c65ba6b517cac18037113,db_config_database:gitlabhq_test,db_config_name:main,line:/ee/app/models/vulnerabilities/identifier.rb:70:in `search_identifier_name_in_group'*/

• explain query: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/34589/commands/106975

SELECT
    SUM("project_security_statistics"."vulnerability_count")
FROM
    "project_security_statistics"
WHERE
    "project_security_statistics"."project_id" IN (
        SELECT
            "projects"."id"
        FROM
            UNNEST(COALESCE((
                    SELECT
                        ids
                    FROM (
                        SELECT
                            "namespace_descendants"."all_project_ids" AS ids
                        FROM "namespace_descendants"
                    WHERE
                        "namespace_descendants"."outdated_at" IS NULL
                        AND "namespace_descendants"."namespace_id" = 9970) cached_query), (
                    SELECT
                        ids
                    FROM (
                        SELECT
                            ARRAY_AGG("projects"."id") AS ids
                        FROM (
                            SELECT
                                "projects"."id"
                            FROM "projects"
                        WHERE
                            "projects"."namespace_id" IN (
                                SELECT
                                    "namespaces"."id"
                                FROM UNNEST(COALESCE((
                                        SELECT
                                            ids
                                        FROM (
                                            SELECT
                                                "namespace_descendants"."self_and_descendant_group_ids" AS ids
                                            FROM "namespace_descendants"
                                        WHERE
                                            "namespace_descendants"."outdated_at" IS NULL
                                            AND "namespace_descendants"."namespace_id" = 9970) cached_query), (
                                        SELECT
                                            ids
                                        FROM (
                                            SELECT
                                                ARRAY_AGG("namespaces"."id") AS ids
                                            FROM (
                                                SELECT
                                                    namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
                                                FROM "namespaces"
                                            WHERE
                                                "namespaces"."type" = 'Group'
                                                AND (traversal_ids @> ('{9970}'))) namespaces) consistent_query))) AS namespaces (id))) projects) consistent_query))) AS projects (id))
    /* allow_cross_joins_across_databases */
    /*application:test,correlation_id:cca8cf66bb71ee50025fde3ac67b6f2b,db_config_database:gitlabhq_test,db_config_name:main,line:/ee/app/models/security/project_statistics.rb:24:in `sum_vulnerability_count_for_group'*/

• explain query: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/34589/commands/106977

Related to #508713 (closed)