Adds adjacentWorkItemId and relativePosition fields to Work Item Update

What does this MR do and why?

Enable manual reordering of work item children by changing children relative position in Work Item Hierarchy Widget. Extends Work Item Update mutation with adjacentWorkItemId and relativePosition fields. Utilises RelativePositioning concern functionality.

Possible relativePosition values are BEFORE, AFTER as per graphql enums styleguide

Edge cases and error responses to keep in mind:

Message	Reason
Relative position cannot be combined with childrenIds.	Either relativePosition or adjacentWorkItemId argument is given together with childrenIds
The adjacent work item\'s parent must match the new parent work item.	When changing parent, adjacentWorkItemId argument contains another hierarchy's item
The adjacent work item\'s parent must match the current parent work item.	When keeping parent, adjacentWorkItemId argument contains another hierarchy's item
Relative position is not valid.	relativePosition is given without adjacentWorkItemId or vice versa

Related to #370401 (closed)

Implements #370401 Task Types::WorkItems::Widgets::HierarchyUpdateInputType accepts adjacentWorkItemId and relativePosition fields

This work is havily based on learnings from Adds moveBeforeId, and moveAfterId fields to Wo... (!109242 - closed) . Please, check !109242 (comment 1312735860) for more details.

DB query details

RelativePositioning adds a lot of methods and therefore, ton of new queries are now possible.

Here I'm listing a query subset which will be used on production by WorkItems::ParentLinks::BaseService and WorkItems::ParentLinks::UpdateService services introduced in this MR. Please, note that frontend part is not yet started and therefore this queries will not affect production until FE: Work Items - Ability to reorder work items ... (#385887 - closed) release.

A small subset of database queries introduced by RelativePositioning functionality:

query 1

SELECT
    "work_item_parent_links".*
FROM
    "work_item_parent_links"
WHERE
    "work_item_parent_links"."work_item_parent_id" = 632
    AND "work_item_parent_links"."id" != 15
    AND "work_item_parent_links"."relative_position" < 50
ORDER BY
    "work_item_parent_links"."relative_position" DESC
LIMIT 1

Query plan (internal): https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/15392/commands/53458

query 2

SELECT
    1 AS one
FROM
    "work_item_parent_links"
WHERE
    "work_item_parent_links"."work_item_id" = 643
    AND "work_item_parent_links"."id" != 15
LIMIT 1

Query plan (internal): https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/15392/commands/53459

query 3

SELECT
    COUNT(*)
FROM
    "work_item_parent_links"
WHERE
    "work_item_parent_links"."work_item_parent_id" = 632

Query plan (internal): https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/15392/commands/53460

query 4

SELECT
    "issues".*
FROM
    "issues"
    INNER JOIN "work_item_parent_links" ON "issues"."id" = "work_item_parent_links"."work_item_id"
WHERE
    "work_item_parent_links"."work_item_parent_id" = 632
ORDER BY
    "work_item_parent_links"."relative_position" ASC NULLS LAST,
    "issues"."created_at" ASC
LIMIT 101

Query plan (internal): https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/15392/commands/53461

query 5

SELECT
    "work_item_parent_links".*
FROM
    "work_item_parent_links"
WHERE
    "work_item_parent_links"."work_item_parent_id" IN (643, 648, 649, 650, 651, 652)

Query plan (internal): https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/15392/commands/53462

How to set up and validate locally

1. Local Test scenario background setup.

• Create a new Issue named Testing Work Items Hierarchy Widget children sorting:
  • Add five tasks to the issue named Task #1, Task #2 ... Task #5.
  • Verify new tasks are added to the end of the list, which is consistent behaviour.
  • Reload the page in the browser, and verify Task #1 appears at the top of the list.
    • Check the GID of Task #1. For current scenario it is "gid://gitlab/WorkItem/643"
    • Check the GID of Task #5. For current scenario it is "gid://gitlab/WorkItem/648"

2. Execute graphQL mutation:

mutation($workItemUpdateInput: WorkItemUpdateInput!) {
  workItemUpdate(input: $workItemUpdateInput) {
    workItem {
  description
  widgets {
    type
    ... on WorkItemWidgetHierarchy {
      parent {
        id
      }
      children {
        edges {
          node {
            id
          }
        }
      }
    }
  }
}
errors

  }
}

with variables

{"workItemUpdateInput":{
  "hierarchyWidget":{
    "adjacentWorkItemId":"gid://gitlab/WorkItem/643",
    "relativePosition":"BEFORE"
  },
  "id":"gid://gitlab/WorkItem/648"
}}

3. Local Test scenario verification.

• Reload Testing Work Items Hierarchy Widget children sorting issue page in the browser and verify:
  • All five tasks are present on the page.
  • Work Item Task #5 appears at the top of the list.

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.