Sort epic ancestors in hierarchical order in graphQL endpoint

What does this MR do and why?

It fixes sorting of epic ancestors in graphQL endpoint. This sorting is then used by the frontend.

Screenshots or screen recordings

How to set up and validate locally

Preparation

1. Go to a group -> epics
2. Create a hierarchy of at least 4 epics (ideally in non-linear order, eg. create epics 1,2,3,4,5 but hierarchy 1,2,4,3,5)

GraphQL

{
  workspace: group(fullPath: "flightjs") {
    id
    issuable: epic(iid: "8") {
      id
      ancestors {
        nodes {
          id
          title
        }
      }
    }
  }
}

UI

1. Go to a group -> epics
2. Open the last child
3. Check the order of the ancestors in the side bar (base parent should be on the first position, direct parent as the last)

Database review

EpicsFinder change

Old query

Query Plan: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7034/commands/24884

Query

SELECT
  "epics".*
FROM
  "epics"
WHERE
  "epics"."group_id" IN (
    SELECT
      "namespaces"."id"
    FROM
      "namespaces"
    WHERE (traversal_ids @> ('{11}')))
  AND "epics"."id" IN ( WITH RECURSIVE "base_and_ancestors" AS (
(
        SELECT
          1 AS depth,
          ARRAY[epics.id] AS tree_path,
          FALSE AS tree_cycle,
          "epics".*
        FROM
          "epics"
        WHERE
          "epics"."id" = 9)
      UNION (
        SELECT
          ("base_and_ancestors"."depth" + 1),
          tree_path || "epics".id,
          "epics".id = ANY (tree_path),
          "epics".*
        FROM
          "epics",
          "base_and_ancestors"
        WHERE
          "epics"."id" = "base_and_ancestors"."parent_id"
          AND "base_and_ancestors"."tree_cycle" = FALSE))
      SELECT
        "id"
      FROM
        "base_and_ancestors" AS "epics"
      WHERE
        "epics"."id" NOT IN (
          SELECT
            "epics"."id"
          FROM
            "epics"
          WHERE
            "epics"."id" = 9)
        ORDER BY
          "depth" ASC)

New query

Query plan: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7034/commands/24885

Query

WITH RECURSIVE "base_and_ancestors" AS (
(
    SELECT
      1 AS depth,
      ARRAY[epics.id] AS tree_path,
      FALSE AS tree_cycle,
      "epics".*
    FROM
      "epics"
    WHERE
      "epics"."id" = 4)
  UNION (
    SELECT
      ("base_and_ancestors"."depth" + 1),
      tree_path || "epics".id,
      "epics".id = ANY (tree_path),
      "epics".*
    FROM
      "epics",
      "base_and_ancestors"
    WHERE
      "epics"."id" = "base_and_ancestors"."parent_id"
      AND "base_and_ancestors"."tree_cycle" = FALSE))
SELECT
  "epics".*
FROM
  "base_and_ancestors" AS "epics"
WHERE
  "epics"."id" NOT IN (
    SELECT
      "epics"."id"
    FROM
      "epics"
    WHERE
      "epics"."id" = 4)
  AND "epics"."id" IN (
    SELECT
      "epics"."id"
    FROM
      "epics"
    WHERE
      "epics"."group_id" IN (
        SELECT
          "namespaces"."id"
        FROM
          "namespaces"
        WHERE (traversal_ids @> ('{3}'))))
ORDER BY
  "depth" ASC

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.

Related to #340578 (closed)