Optimize pagination query for inherited variables resolver

What does this MR do and why?

Related to issue: #413418 (closed)

Optimize GraphQL resolver instead of batching all variables and paginating against array introduces a single SQL query to fetch variables for all requested groups in one SQL query.

Screenshots or screen recordings

Before:

  Ci::GroupVariable Load (0.2ms)  SELECT "ci_group_variables".* FROM "ci_group_variables" WHERE "ci_group_variables"."group_id" = 25 
  Ci::GroupVariable Load (0.2ms)  SELECT "ci_group_variables".* FROM "ci_group_variables" WHERE "ci_group_variables"."group_id" = 83 

After

  SELECT "ci_group_variables".* FROM "ci_group_variables" WHERE "ci_group_variables"."group_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (25, 83)) ORDER BY "ci_group_variables"."id" DESC LIMIT 6 

Database Queries

SELECT "ci_group_variables".* FROM "ci_group_variables" WHERE "ci_group_variables"."group_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (62954852, 56722522)) ORDER BY "ci_group_variables"."id" DESC LIMIT 100

query plan

Time: 38.836 ms

How to set up and validate locally

1. Navigate to the graphql explorer

http://localhost:3000/-/graphql-explorer. # can be different based on gdk config

1. Copy and paste and run the following query to the graphql explorer to test:

query getInheritedCiVariables {
  project(fullPath: "gnuwget/subwget/projsubwget") {
    id
    inheritedCiVariables(first: 5) {
      pageInfo {
        hasNextPage
        hasPreviousPage
        startCursor
        endCursor
      }
      nodes {
        __typename
        id
        key
        variableType
        environmentScope
        groupCiCdSettingsPath
        groupName
        masked
        protected
        raw
      }
    }
  }
}

Example of output:

{
  "data": {
    "project": {
      "id": "gid://gitlab/Project/25",
      "inheritedCiVariables": {
        "pageInfo": {
          "hasNextPage": true,
          "hasPreviousPage": false,
          "startCursor": "eyJpZCI6IjIwIn0",
          "endCursor": "eyJpZCI6IjE2In0"
        },
        "nodes": [
          {
            "__typename": "InheritedCiVariable",
            "id": "gid://gitlab/Ci::GroupVariable/20",
            "key": "sdfsdfdsfsd",
            "variableType": "ENV_VAR",
            "environmentScope": "*",
            "groupCiCdSettingsPath": "/groups/gnuwget/-/settings/ci_cd",
            "groupName": "Gnuwget",
            "masked": false,
            "protected": true,
            "raw": false
          }]
     }
 }

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.