Skip to content

Filter by status with mapping

Marc Saleikorequested to merge
ms/filter-by-status-with-mapping into master

What does this MR do and why?

This MR adds filtering by status with mapping. Mappings can be added when

  1. a status gets removed from a lifecycle and a replacement needs to be defined
  2. a work item type gets attached to another lifecycle and replacements need to be defined

Mappings define a new status for a given old status and are tied to a work item type and can have a validity (valid_from and valid_until timestamps), but they never overlap with other mappings.

The work item lists and board lists need to respect mappings when they filter for items. We add an OR condition for each existing mapping to the filtered status to the query.

See the description of the first mapping management MR for additional details about the mapping logic.

This is part of a series of MRs to implement this feature:

  1. Adds work_item_custom_status_mapping table and ... (!203127 - merged)
  2. Add date range validation to work item status m... (!203961 - merged)
  3. Realtime status mapping resolution (!204229 - merged) (resolving work item statuses with mappings)
  4. Adding/managing mappings
    1. Lifecycle Update mutation
    2. Lifecycle AttachWorkItemType mutation
  5. 🎯 we're here Filtering with mapping

References

  1. Filter by status based on mapping (#566528 - closed)
  2. BE: Add mapping (#558275 - closed)
  3. Draft: POC for mappings approach for custom sta... (!199448)

Queries and query plans

These queries are the regular work item/issue queries only with the status filter applied. The addition is the mapping resolution which adds this OR condition to the query:

OR "work_item_current_statuses"."custom_status_id" IS NOT NULL
AND "work_item_current_statuses"."custom_status_id" = 31
AND "work_item_current_statuses"."updated_at" <= '2025-09-23 15:00:25.498402'
AND "issues"."work_item_type_id" = 1

In this example 31 is the old_status that should also match the query for a given timeframe. The mapping I added for the plans was a mapping with valid_until to showcase the boundary behavior.

If we have a mapping with time constraint and converted system-defined status lookup a query might look like this:

work_item_current_statuses.updated_at BETWEEN '2025-09-23 18:15:51.899117'
        AND '2025-09-25 18:15:51.899194'
        AND issues.work_item_type_id = 1
        AND (work_item_current_statuses.custom_status_id IS NOT NULL
            AND work_item_current_statuses.custom_status_id = 31
            OR work_item_current_statuses.system_defined_status_id IS NOT NULL
            AND work_item_current_statuses.system_defined_status_id = 2)

Query on the group level

See query plan in postgres.ai (and previous plan from this MR) and query plan for old query with status filtering.

Click to expand 
SELECT "issues"."id",
         "issues"."title",
         "issues"."author_id",
         "issues"."project_id",
         "issues"."created_at",
         "issues"."updated_at",
         "issues"."description",
         "issues"."milestone_id",
         "issues"."iid",
         "issues"."updated_by_id",
         "issues"."weight",
         "issues"."confidential",
         "issues"."due_date",
         "issues"."moved_to_id",
         "issues"."lock_version",
         "issues"."title_html",
         "issues"."description_html",
         "issues"."time_estimate",
         "issues"."relative_position",
         "issues"."service_desk_reply_to",
         "issues"."cached_markdown_version",
         "issues"."last_edited_at",
         "issues"."last_edited_by_id",
         "issues"."discussion_locked",
         "issues"."closed_at",
         "issues"."closed_by_id",
         "issues"."state_id",
         "issues"."duplicated_to_id",
         "issues"."promoted_to_epic_id",
         "issues"."health_status",
         "issues"."sprint_id",
         "issues"."blocking_issues_count",
         "issues"."upvotes_count",
         "issues"."work_item_type_id",
         "issues"."namespace_id",
         "issues"."start_date",
         "issues"."imported_from",
         "issues"."namespace_traversal_ids"
FROM "issues"
INNER JOIN "projects"
    ON "projects"."id" = "issues"."project_id" LEFT OUTER
JOIN "work_item_current_statuses"
    ON "work_item_current_statuses"."work_item_id" = "issues"."id"
LEFT JOIN project_features
    ON projects.id = project_features.project_id
WHERE "projects"."namespace_id" IN 
    (SELECT "namespaces"."id"
    FROM UNNEST( COALESCE( 
        (SELECT ids
        FROM 
            (SELECT "namespace_descendants"."self_and_descendant_group_ids" AS ids
            FROM "namespace_descendants"
            WHERE "namespace_descendants"."outdated_at" IS NULL
                    AND "namespace_descendants"."namespace_id" = 9970) cached_query), 
                (SELECT ids
                FROM 
                    (SELECT ARRAY_AGG("namespaces"."id") AS ids
                    FROM 
                        (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids,
         1)] AS id
                        FROM "namespaces"
                        WHERE "namespaces"."type" = 'Group'
                                AND (traversal_ids @> ('{9970}'))) namespaces) consistent_query)) ) AS namespaces(id) )
                            AND (EXISTS 
                        (SELECT 1
                        FROM "project_authorizations"
                        WHERE "project_authorizations"."user_id" = 12977570
                                AND (project_authorizations.project_id = projects.id))
                                OR projects.visibility_level IN (0,10,20))
                            AND ("project_features"."issues_access_level" > 0
                            OR "project_features"."issues_access_level" IS NULL)
                        AND "issues"."state_id" = 1
                    AND ("issues"."project_id" IS NULL
                    OR "projects"."archived" = FALSE)
                AND "issues"."work_item_type_id" IN (1, 2, 5, 7, 6, 3)
            AND (work_item_current_statuses.custom_status_id IS NOT NULL
        AND work_item_current_statuses.custom_status_id = 32
        OR work_item_current_statuses.updated_at
    BETWEEN '2025-09-23 18:15:51.899117'
        AND '2025-09-25 18:15:51.899194'
        AND issues.work_item_type_id = 1
        AND (work_item_current_statuses.custom_status_id IS NOT NULL
        AND work_item_current_statuses.custom_status_id = 31
        OR work_item_current_statuses.system_defined_status_id IS NOT NULL
        AND work_item_current_statuses.system_defined_status_id = 1))
ORDER BY  "issues"."created_at" DESC, "issues"."id" DESC LIMIT 21

Query on the project level

See query plan in postgres.ai (and previous plan of this MR) and query plan for old query with status filtering.

Click to expand 
SELECT
    "issues"."id",
    "issues"."title",
    "issues"."author_id",
    "issues"."project_id",
    "issues"."created_at",
    "issues"."updated_at",
    "issues"."description",
    "issues"."milestone_id",
    "issues"."iid",
    "issues"."updated_by_id",
    "issues"."weight",
    "issues"."confidential",
    "issues"."due_date",
    "issues"."moved_to_id",
    "issues"."lock_version",
    "issues"."title_html",
    "issues"."description_html",
    "issues"."time_estimate",
    "issues"."relative_position",
    "issues"."service_desk_reply_to",
    "issues"."cached_markdown_version",
    "issues"."last_edited_at",
    "issues"."last_edited_by_id",
    "issues"."discussion_locked",
    "issues"."closed_at",
    "issues"."closed_by_id",
    "issues"."state_id",
    "issues"."duplicated_to_id",
    "issues"."promoted_to_epic_id",
    "issues"."health_status",
    "issues"."sprint_id",
    "issues"."blocking_issues_count",
    "issues"."upvotes_count",
    "issues"."work_item_type_id",
    "issues"."namespace_id",
    "issues"."start_date",
    "issues"."imported_from",
    "issues"."namespace_traversal_ids"
FROM
    "issues"
    INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
    LEFT OUTER JOIN "work_item_current_statuses" ON "work_item_current_statuses"."work_item_id" = "issues"."id"
    LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS (
        SELECT
            1
        FROM
            "project_authorizations"
        WHERE
            "project_authorizations"."user_id" = 12977570
            AND (project_authorizations.project_id = projects.id))
        OR projects.visibility_level IN (0, 10, 20))
    AND ("project_features"."issues_access_level" > 0
        OR "project_features"."issues_access_level" IS NULL)
    AND "projects"."id" = 278964
    AND "issues"."state_id" = 1
    AND "issues"."work_item_type_id" IN (1, 2, 5, 8, 7, 6, 3)
    AND (work_item_current_statuses.custom_status_id IS NOT NULL
        AND work_item_current_statuses.custom_status_id = 32
        OR work_item_current_statuses.updated_at BETWEEN '2025-09-23 18:15:51.899117'
        AND '2025-09-25 18:15:51.899194'
        AND issues.work_item_type_id = 1
        AND (work_item_current_statuses.custom_status_id IS NOT NULL
            AND work_item_current_statuses.custom_status_id = 31
            OR work_item_current_statuses.system_defined_status_id IS NOT NULL
            AND work_item_current_statuses.system_defined_status_id = 2))
ORDER BY
    "issues"."created_at" DESC,
    "issues"."id" DESC
LIMIT 21
-- Formatted by pgFormatter::Beautify

Screenshots or screen recordings

How to set up and validate locally

  1. Create a new group

  2. Add custom statuses for the group through the UI via group settings --> issues --> edit statuses. Also include statuses "old status" and "new status" in the "todo" category for easier validation

  3. Create a project and add two issues in the project. One with the status "old status" and one with the status "new status".

  4. Now create a mapping via the console from "old status" to "new status". You can find the IDs using the settings page and investigating the query that returns the statuses.

    WorkItems::Statuses::Custom::Mapping.create!(namespace: Group.find(INSERT_GROUP_ID), work_item_type_id: 1,
  old_status_id: OLD_STATUS_ID, new_status_id: NEW_STATUS_ID)

  5. Go to the project issue list and filter by "new status" and see that both issues match the filter query.

  6. Go to the group issue list and filter by "new status" and see that both issues match the filter query.

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.

Edited by Marc Saleiko

Merge request reports