Graphql query for listing project compliance violations for a group

What does this MR do and why?

This MR adds a GraphQL query for listing all compliance violations for projects under a group and its subgroups.

Database

Query plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/39919/commands/123151

Click to expand SQL

SELECT 
  * 
FROM 
  (
    WITH RECURSIVE "array_cte" AS MATERIALIZED (
      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" = 88597607
                ) 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 @> ('{88597607}')
                        )
                    ) namespaces
                ) consistent_query
            )
          )
        ) AS namespaces(id)
    ), 
    "recursive_keyset_cte" AS (
      (
        SELECT 
          NULL :: project_compliance_violations AS records, 
          array_cte_id_array, 
          project_compliance_violations_created_at_array, 
          project_compliance_violations_id_array, 
          0 :: bigint AS count 
        FROM 
          (
            SELECT 
              ARRAY_AGG("array_cte"."id") AS array_cte_id_array, 
              ARRAY_AGG(
                "project_compliance_violations"."created_at"
              ) AS project_compliance_violations_created_at_array, 
              ARRAY_AGG(
                "project_compliance_violations"."id"
              ) AS project_compliance_violations_id_array 
            FROM 
              (
                SELECT 
                  "array_cte"."id" 
                FROM 
                  array_cte
              ) array_cte 
              LEFT JOIN LATERAL (
                SELECT 
                  "project_compliance_violations"."created_at" AS created_at, 
                  "project_compliance_violations"."id" AS id 
                FROM 
                  "project_compliance_violations" 
                WHERE 
                  "project_compliance_violations"."namespace_id" = "array_cte"."id" 
                ORDER BY 
                  "project_compliance_violations"."created_at" DESC, 
                  "project_compliance_violations"."id" DESC 
                LIMIT 
                  1
              ) project_compliance_violations ON TRUE 
            WHERE 
              "project_compliance_violations"."created_at" IS NOT NULL 
              AND "project_compliance_violations"."id" IS NOT NULL
          ) array_scope_lateral_query 
        LIMIT 
          1
      ) 
      UNION ALL 
        (
          SELECT 
            (
              SELECT 
                project_compliance_violations 
              FROM 
                "project_compliance_violations" 
              WHERE 
                "project_compliance_violations"."id" = recursive_keyset_cte.project_compliance_violations_id_array[position] 
              LIMIT 
                1
            ), array_cte_id_array, 
            recursive_keyset_cte.project_compliance_violations_created_at_array[ : position_query.position - 1] || next_cursor_values.created_at || recursive_keyset_cte.project_compliance_violations_created_at_array[position_query.position + 1 : ], 
            recursive_keyset_cte.project_compliance_violations_id_array[ : position_query.position - 1] || next_cursor_values.id || recursive_keyset_cte.project_compliance_violations_id_array[position_query.position + 1 : ], 
            recursive_keyset_cte.count + 1 
          FROM 
            recursive_keyset_cte, 
            LATERAL (
              SELECT 
                created_at, 
                id, 
                position 
              FROM 
                UNNEST(
                  project_compliance_violations_created_at_array, 
                  project_compliance_violations_id_array
                ) WITH ORDINALITY AS u(created_at, id, position) 
              WHERE 
                created_at IS NOT NULL 
                AND id IS NOT NULL 
              ORDER BY 
                1 DESC, 
                2 DESC 
              LIMIT 
                1
            ) AS position_query, 
            LATERAL (
              SELECT 
                "record"."created_at", 
                "record"."id" 
              FROM 
                (
                  VALUES 
                    (NULL, NULL)
                ) AS nulls 
                LEFT JOIN (
                  SELECT 
                    "project_compliance_violations"."created_at" AS created_at, 
                    "project_compliance_violations"."id" AS id 
                  FROM 
                    "project_compliance_violations" 
                  WHERE 
                    "project_compliance_violations"."namespace_id" = recursive_keyset_cte.array_cte_id_array[position] 
                    AND (
                      (
                        "project_compliance_violations"."created_at", 
                        "project_compliance_violations"."id"
                      ) < (
                        recursive_keyset_cte.project_compliance_violations_created_at_array[position], 
                        recursive_keyset_cte.project_compliance_violations_id_array[position]
                      )
                    ) 
                  ORDER BY 
                    "project_compliance_violations"."created_at" DESC, 
                    "project_compliance_violations"."id" DESC 
                  LIMIT 
                    1
                ) record ON TRUE 
              LIMIT 
                1
            ) AS next_cursor_values
        )
    ) 
    SELECT 
      (records).* 
    FROM 
      "recursive_keyset_cte" AS "project_compliance_violations" 
    WHERE 
      (count <> 0)
  ) project_compliance_violations 
LIMIT 
  100;

How to set up and validate locally

1. You need to have a group with Ultimate license.
2. To seed the data for creating violations under a project:
   1. Checkout the branch hraghuvanshi-comp-violations-seeder.
   2. Run the command in bash for generating the violations FILTER=compliance_report_data SEED_COMPLIANCE_REPORT_DATA=1 GROUP_ID=<group_id> bundle exec rake db:seed_fu.
3. Now checkout this branch.
4. Run the following graphql query, you will get a list of all the compliance violations for the projects under this group:

query groupComplianceViolations {
  group(fullPath: "<group_full_path>") {
    id
    name
    projectComplianceViolations {
      nodes {
        id
        createdAt
        project {
          id
          name
        }
        complianceControl {
          id
          name
        }
        status
      }
    }
  }
}

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #542342 (closed)