Sort work items by status

What does this MR do and why?

This MR implements sorting functionality for work items that belong to multiple namespaces by their status in both ascending and descending order. It adds the logic needed to support the previously added STATUS_ASC and STATUS_DESC sorting options in the Work Items API via !201401 (merged).

As per this decision, work item status migrations and backfills are not yet implemented. For now, we rely on status mapping and fallback logic. The new order_status_asc and order_status_desc scopes handle these scenarios, covering both explicitly and implicitly set statuses.

Work items are first sorted by their status category. If two items share the same category, the system falls back to sorting by work item ID.

References

• BE: Add support for sorting by Status (#550262 - closed)
• Configurable Work Item Statuses Design Doc

Query plans

Query plans were executed for the gitlab-org/gitlab project (ID: 278964).

1. Sort work items by status in ascending order: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/43534/commands/132941.
2. Sort work items by status in descending order: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/43534/commands/132942.

Screenshots or screen recordings

System-defined statuses	Custom statuses
Screen_Recording_2025-09-09_at_3.16.01_pm	Screen_Recording_2025-09-09_at_3.20.51_pm

How to set up and validate locally

1. Run the queries below to test sorting work items by status.
2. Test across different namespaces that use either system-defined or custom statuses. To set up custom statuses for a root group, follow the instructions here.
3. Use a variety of statuses, ensuring some are explicitly set and others remain implicit.

Click to expand

query {
  project(fullPath: "flightjs/Flight") {
    workItems(
      sort: STATUS_ASC
    ) {
      edges {
        node {
          id
          title
          widgets {
            ... on WorkItemWidgetStatus {
              status {
                name
                category
              }
            }
          }
        }
      }
    }
  }
}

query {
  project(fullPath: "flightjs/Flight") {
    workItems(
      sort: STATUS_DESC
    ) {
      edges {
        node {
          id
          title
          widgets {
            ... on WorkItemWidgetStatus {
              status {
                name
                category
              }
            }
          }
        }
      }
    }
  }
}

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.