Add support for missing parent filtering on work items queries (!200140) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

This MR adds support for parent wildcard id filtering on work items queries, as well as negated parent id filtering, to bring parity with the legacy issues API. The intention here is to match the behaviour of the existing epic filtering using the work item parent links table, while maintaining the performance of the existing queries.

References

#548093 (closed)

Screenshots or screen recordings

Not provided

How to set up and validate locally

In an environment with the GDK running, navigate to http://localhost:3000/-/graphql-explorer
In a project, create a hierarchical relationship between 2 work items by setting the parent. For example by:
- Setting the parent of an issue to an epic
- Setting the parent of a task to an issue
Create some more work items without assigning a parent
Write a query to fetch work items in the project, specifying parentWildcardId: ANY. e.g:

query q {
  namespace(fullPath: "flightjs/flight") {
    workItems(parentWildcardId: ANY) {
      nodes {
        title
      }
    }
  }
}

Observe that only work items assigned a parent are returned in the query response
Repeat the above, and set parentWildcardId: NONE. Observe that only work items without a parent are returned in the query
Write a query to fetch work items who do not have a specific parent with the not: { parent_ids } arg. e.g:

query q {
  namespace(fullPath: "flightjs/flight") {
    workItems(not: { parentIds: ["gid://gitlab/WorkItem/748"] }) {
      nodes {
        title
      }
    }
  }
}

Observe the results include only work items who do not have the specified parent
Repeat the above queries on a group / group namespace instead of a project. Observe that the filters have the same effect

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

Raw SQL

Project Level Queries

Legacy Query `ANY`

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)
ORDER BY "issues"."updated_at" DESC, "issues"."id" DESC
LIMIT 21

New Query `ANY`

SELECT "issues".*
FROM "issues"
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 (EXISTS (SELECT 1 FROM "work_item_parent_links" WHERE "work_item_parent_links"."work_item_id" = "issues"."id"))
ORDER BY "issues"."updated_at" DESC, "issues"."id" DESC
LIMIT 21

Legacy Query `NONE`

SELECT "issues".*
FROM "issues"
         LEFT OUTER 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" IS NULL
ORDER BY "issues"."updated_at" DESC, "issues"."id" DESC
LIMIT 21

New Query `NONE`

SELECT "issues".*
FROM "issues"
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 (NOT EXISTS (SELECT 1
                   FROM "work_item_parent_links"
                   WHERE "work_item_parent_links"."work_item_id" = "issues"."id"))
ORDER BY "issues"."updated_at" DESC, "issues"."id" DESC
LIMIT 21

Legacy Query `!=`

SELECT "issues".*
FROM "issues"
         LEFT OUTER 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 NOT IN (273703) OR epic_issues.epic_id IS NULL)
ORDER BY "issues"."updated_at" DESC, "issues"."id" DESC
LIMIT 21

New Query `!=`

SELECT "issues".*
FROM "issues"
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 (NOT EXISTS (SELECT 1
                   FROM "work_item_parent_links"
                   WHERE "work_item_parent_links"."work_item_id" = "issues"."id"
                     AND "work_item_parent_links"."work_item_parent_id" IN (145898415)))
ORDER BY "issues"."updated_at" DESC, "issues"."id" DESC
LIMIT 21

Group Level Queries