Add include_descendant_work_items argument to work items query

What does this MR do and why?

This MR introduces the include_descendant_work_items argument on the work item query, which aims to match the behaviour of the include_subepics argument on the existing issues queries.

The arg for work items follows a similar approach to the existing include_subepics filter here, which descends the hierarchy recursively to fetch the parents before filtering on issues.

Note that there's a slight change in behaviour for work items, where we descend the hierarchy regardless of the work item types. For the legacy issues endpoint, we previously only descended through the epics.

References

• #547848 (closed)

Screenshots or screen recordings

Not provided

How to set up and validate locally

1. With the GDK running, navigate to http://localhost:3000/-/graphql-explorer
2. Create an epic E, with a child issue I
3. Create a task T, and set the parent to I
4. Write a query to fetch a project's work items with a parent filter applied to the epic. Also pass includeDescendantWorkItems: true. e.g:

query q {
  project(fullPath: "flightjs/flight") {
    workItems(parentIds: ["gid://gitlab/WorkItem/633"], includeDescendantWorkItems: true) {
      nodes {
        id
        title
      }
    }
  }
}

5. Observe that both issue I and the task T are returned, since they're both under the hierarchy of epic E
6. Pass includeDescendantWorkItems: false, and observe that only issue I is returned. e.g:

query q {
  project(fullPath: "flightjs/flight") {
    workItems(parentIds: ["gid://gitlab/WorkItem/633"], includeDescendantWorkItems: false) {
      nodes {
        id
        title
      }
    }
  }
}

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.

Database

Legacy Issues Filter Raw SQL

SELECT "issues".*
FROM "issues"
         INNER JOIN "epic_issues" ON "epic_issues"."issue_id" = "issues"."id"
WHERE (NOT EXISTS (SELECT 1 FROM "banned_users" WHERE (banned_users.user_id = (issues.author_id + 0))))
  AND "issues"."project_id" = 278964
  AND "issues"."state_id" = 1
  AND "issues"."work_item_type_id" IN (1, 2, 5, 7, 6, 3)
  AND "epic_issues"."epic_id" IN
      (WITH RECURSIVE "base_and_descendants" AS ((SELECT "epics".* FROM "epics" WHERE "epics"."id" = 273703)
                                                 UNION
                                                 (SELECT "epics".*
                                                  FROM "epics",
                                                       "base_and_descendants"
                                                  WHERE "epics"."parent_id" = "base_and_descendants"."id"))
       SELECT "id"
       FROM "base_and_descendants" AS "epics")
ORDER BY "issues"."updated_at" DESC, "issues"."id" DESC
LIMIT 21

New Work Items Filter Raw SQL

SELECT "issues".*
FROM "issues"
         INNER JOIN work_item_parent_links ON work_item_parent_links.work_item_id = issues.id
WHERE (NOT EXISTS (SELECT 1 FROM "banned_users" WHERE (banned_users.user_id = (issues.author_id + 0))))
  AND "issues"."project_id" = 278964
  AND "issues"."state_id" = 1
  AND "issues"."work_item_type_id" IN (1, 2, 5, 7, 6, 3)
  AND "work_item_parent_links"."work_item_parent_id" IN
      (WITH RECURSIVE "base_and_descendants" AS ((SELECT "issues".*
                                                  FROM "issues"
                                                  WHERE "issues"."id" = 145898415)
                                                 UNION
                                                 (SELECT "issues".*
                                                  FROM "issues",
                                                       "base_and_descendants",
                                                       "work_item_parent_links"
                                                  WHERE "work_item_parent_links"."work_item_id" = "issues"."id"
                                                    AND
                                                      "work_item_parent_links"."work_item_parent_id" =
                                                      "base_and_descendants"."id"))
       SELECT "id"
       FROM "base_and_descendants" AS "issues")
ORDER BY "issues"."updated_at" DESC, "issues"."id" DESC
LIMIT 21

Query Plans:

Tested with filtering on this epic, which has a significant number of child epics and issues under gitlab-org/gitlab

Existing issues query	New work items query
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/41796/commands/128265	https://console.postgres.ai/gitlab/gitlab-production-main/sessions/41796/commands/128267

Related to #547848 (closed)