From f1cb7c86225491387a4c07508e0aece83da432c3 Mon Sep 17 00:00:00 2001 From: Agnes Slota Date: Tue, 23 Sep 2025 17:09:35 +1000 Subject: [PATCH 1/4] Add status mapping support to work item sorting Enhance the existing work item status sorting to support status mappings. The implementation: - Updates the status sorting logic to consider mapped statuses. - Handles both direct custom status mappings and system-defined status conversions. - Supports time-based validity for status mappings with valid_from/valid_until. Changelog: added EE: true --- .../models/concerns/work_items/has_status.rb | 26 ++++++++- ee/spec/models/work_item_spec.rb | 58 +++++++++++++++++++ 2 files changed, 83 insertions(+), 1 deletion(-) diff --git a/ee/app/models/concerns/work_items/has_status.rb b/ee/app/models/concerns/work_items/has_status.rb index 091f9d094cefa4..af8aeb857c2751 100644 --- a/ee/app/models/concerns/work_items/has_status.rb +++ b/ee/app/models/concerns/work_items/has_status.rb @@ -108,9 +108,33 @@ def self.generate_status_order_sql <<-SQL.squish CASE WHEN work_item_current_statuses.custom_status_id IS NOT NULL THEN - work_item_custom_statuses.category + COALESCE( + (SELECT mapped_statuses.category + FROM work_item_custom_status_mappings + JOIN work_item_custom_statuses mapped_statuses ON mapped_statuses.id = work_item_custom_status_mappings.new_status_id + WHERE work_item_custom_status_mappings.old_status_id = work_item_current_statuses.custom_status_id + AND work_item_custom_status_mappings.work_item_type_id = #{table_name}.work_item_type_id + AND work_item_custom_status_mappings.namespace_id = namespaces.traversal_ids[1] + AND (work_item_custom_status_mappings.valid_from IS NULL OR work_item_custom_status_mappings.valid_from <= work_item_current_statuses.updated_at) + AND (work_item_custom_status_mappings.valid_until IS NULL OR work_item_custom_status_mappings.valid_until > work_item_current_statuses.updated_at) + ORDER BY work_item_custom_status_mappings.valid_from DESC NULLS LAST + LIMIT 1), + work_item_custom_statuses.category + ) WHEN work_item_current_statuses.system_defined_status_id IS NOT NULL THEN COALESCE( + (SELECT mapped_statuses.category + FROM work_item_custom_statuses inner_converted_statuses + JOIN work_item_custom_status_mappings ON work_item_custom_status_mappings.old_status_id = inner_converted_statuses.id + JOIN work_item_custom_statuses mapped_statuses ON mapped_statuses.id = work_item_custom_status_mappings.new_status_id + WHERE inner_converted_statuses.converted_from_system_defined_status_identifier = work_item_current_statuses.system_defined_status_id + AND inner_converted_statuses.namespace_id = namespaces.traversal_ids[1] + AND work_item_custom_status_mappings.work_item_type_id = #{table_name}.work_item_type_id + AND work_item_custom_status_mappings.namespace_id = namespaces.traversal_ids[1] + AND (work_item_custom_status_mappings.valid_from IS NULL OR work_item_custom_status_mappings.valid_from <= work_item_current_statuses.updated_at) + AND (work_item_custom_status_mappings.valid_until IS NULL OR work_item_custom_status_mappings.valid_until > work_item_current_statuses.updated_at) + ORDER BY work_item_custom_status_mappings.valid_from DESC NULLS LAST + LIMIT 1), converted_statuses.category, CASE work_item_current_statuses.system_defined_status_id #{system_defined_status_cases} END ) diff --git a/ee/spec/models/work_item_spec.rb b/ee/spec/models/work_item_spec.rb index 80923af938eece..db5e69968662ae 100644 --- a/ee/spec/models/work_item_spec.rb +++ b/ee/spec/models/work_item_spec.rb @@ -886,6 +886,64 @@ end end + context 'with status mappings' do + let_it_be(:custom_to_do) do + create(:work_item_custom_status, :without_conversion_mapping, category: :to_do, + namespace: reusable_group, lifecycles: [lifecycle]) + end + + let_it_be(:mapped_in_progress) do + create(:work_item_custom_status, :without_conversion_mapping, category: :in_progress, + namespace: reusable_group, lifecycles: [lifecycle]) + end + + let_it_be(:custom_status_mapping) do + create(:work_item_custom_status_mapping, + old_status_id: lifecycle.default_open_status_id, + new_status_id: mapped_in_progress.id, + work_item_type_id: issue_work_item_type.id, + namespace_id: reusable_group.id + ) + end + + let_it_be(:wi_custom_mapped_in_progress) do + create(:work_item, project: project, custom_status_id: lifecycle.default_open_status_id) + end + + let_it_be(:wi_sd_mapped_in_progress) { create(:work_item, project: project) } + + let_it_be(:wi_sd_mapped_in_progress_current_status) do + # Skip validations since we are simulating an old record + # when the namespace still used the system defined lifecycle + build(:work_item_current_status, + work_item: wi_sd_mapped_in_progress, + system_defined_status_id: system_defined_todo_status.id + ).save!(validate: false) + end + + let(:work_items) do + [ + wi_custom, wi_custom_mapped_in_progress, wi_sd_mapped_in_progress, + wi_converted_in_progress, wi_custom_done, wi_custom_duplicated, wi_default_open, + wi_default_closed, wi_default_duplicated + ] + end + + it 'sorts work items by status asc' do + expect(result).to eq([ + wi_custom, + wi_default_open, + wi_sd_mapped_in_progress, + wi_custom_mapped_in_progress, + wi_converted_in_progress, + wi_custom_done, + wi_default_closed, + wi_custom_duplicated, + wi_default_duplicated + ]) + end + end + context 'with system-defined and custom statuses from multiple namespaces' do let(:work_items) do [ -- GitLab From f96a2ba43609da94e4ace383b75241399f2fd398 Mon Sep 17 00:00:00 2001 From: Agnes Slota Date: Mon, 29 Sep 2025 09:00:56 +1000 Subject: [PATCH 2/4] Remove unnecessary ORDER BY in status sorting MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit When retrieving custom status mappings, we previously ordered results by valid_from DESC NULLS LAST before applying LIMIT 1. Since there can only be one mapping that matches the time constraints (where valid_from ≤ updated_at < valid_until), this ordering is unnecessary and can be removed for a minor performance improvement. --- ee/app/models/concerns/work_items/has_status.rb | 2 -- 1 file changed, 2 deletions(-) diff --git a/ee/app/models/concerns/work_items/has_status.rb b/ee/app/models/concerns/work_items/has_status.rb index af8aeb857c2751..feddda878e1c25 100644 --- a/ee/app/models/concerns/work_items/has_status.rb +++ b/ee/app/models/concerns/work_items/has_status.rb @@ -117,7 +117,6 @@ def self.generate_status_order_sql AND work_item_custom_status_mappings.namespace_id = namespaces.traversal_ids[1] AND (work_item_custom_status_mappings.valid_from IS NULL OR work_item_custom_status_mappings.valid_from <= work_item_current_statuses.updated_at) AND (work_item_custom_status_mappings.valid_until IS NULL OR work_item_custom_status_mappings.valid_until > work_item_current_statuses.updated_at) - ORDER BY work_item_custom_status_mappings.valid_from DESC NULLS LAST LIMIT 1), work_item_custom_statuses.category ) @@ -133,7 +132,6 @@ def self.generate_status_order_sql AND work_item_custom_status_mappings.namespace_id = namespaces.traversal_ids[1] AND (work_item_custom_status_mappings.valid_from IS NULL OR work_item_custom_status_mappings.valid_from <= work_item_current_statuses.updated_at) AND (work_item_custom_status_mappings.valid_until IS NULL OR work_item_custom_status_mappings.valid_until > work_item_current_statuses.updated_at) - ORDER BY work_item_custom_status_mappings.valid_from DESC NULLS LAST LIMIT 1), converted_statuses.category, CASE work_item_current_statuses.system_defined_status_id #{system_defined_status_cases} END -- GitLab From 2659ecb403325762d083e37810e8edeecc34775b Mon Sep 17 00:00:00 2001 From: Agnes Slota Date: Mon, 29 Sep 2025 09:07:02 +1000 Subject: [PATCH 3/4] Add tests for time-bounded status mappings Test both valid_until and valid_from constraints to ensure status mappings are correctly applied based on work item timestamps during sorting. --- ee/spec/models/work_item_spec.rb | 39 +++++++++++++++++++++++++++++++- 1 file changed, 38 insertions(+), 1 deletion(-) diff --git a/ee/spec/models/work_item_spec.rb b/ee/spec/models/work_item_spec.rb index db5e69968662ae..910ad9bfb6a362 100644 --- a/ee/spec/models/work_item_spec.rb +++ b/ee/spec/models/work_item_spec.rb @@ -897,6 +897,11 @@ namespace: reusable_group, lifecycles: [lifecycle]) end + let_it_be(:mapped_in_progress_2) do + create(:work_item_custom_status, :without_conversion_mapping, category: :in_progress, + namespace: reusable_group, lifecycles: [lifecycle]) + end + let_it_be(:custom_status_mapping) do create(:work_item_custom_status_mapping, old_status_id: lifecycle.default_open_status_id, @@ -906,6 +911,26 @@ ) end + let_it_be(:expired_custom_status_mapping) do + create(:work_item_custom_status_mapping, + old_status_id: custom_to_do.id, + new_status_id: mapped_in_progress.id, + work_item_type_id: issue_work_item_type.id, + namespace_id: reusable_group.id, + valid_until: 1.day.ago + ) + end + + let_it_be(:future_custom_status_mapping) do + create(:work_item_custom_status_mapping, + old_status_id: custom_to_do.id, + new_status_id: mapped_in_progress_2.id, + work_item_type_id: issue_work_item_type.id, + namespace_id: reusable_group.id, + valid_from: 1.day.from_now + ) + end + let_it_be(:wi_custom_mapped_in_progress) do create(:work_item, project: project, custom_status_id: lifecycle.default_open_status_id) end @@ -921,18 +946,30 @@ ).save!(validate: false) end + let_it_be(:wi_with_time_bounded_mapping) do + create(:work_item, project: project, custom_status_id: custom_to_do.id).tap do |wi| + wi.current_status.update!(updated_at: 2.days.ago) + end + end + + let_it_be(:wi_with_future_mapping) do + create(:work_item, project: project, custom_status_id: custom_to_do.id) + end + let(:work_items) do [ wi_custom, wi_custom_mapped_in_progress, wi_sd_mapped_in_progress, wi_converted_in_progress, wi_custom_done, wi_custom_duplicated, wi_default_open, - wi_default_closed, wi_default_duplicated + wi_default_closed, wi_default_duplicated, wi_with_time_bounded_mapping, wi_with_future_mapping ] end it 'sorts work items by status asc' do expect(result).to eq([ + wi_with_future_mapping, wi_custom, wi_default_open, + wi_with_time_bounded_mapping, wi_sd_mapped_in_progress, wi_custom_mapped_in_progress, wi_converted_in_progress, -- GitLab From 272d8b350fc1a46c60bb34ebbd6e21b7395f3fce Mon Sep 17 00:00:00 2001 From: Agnes Slota Date: Mon, 29 Sep 2025 09:51:19 +1000 Subject: [PATCH 4/4] Optimise work item status sorting with mapping This commit optimises the subquery approach for work item status sorting by: 1. Skipping subqueries entirely for namespaces without mappings. 2. Adding early exit conditions for statuses without mappings. These optimizations significantly reduce the number of subqueries executed, especially for namespaces without mappings, while maintaining the same functionality. --- .../models/concerns/work_items/has_status.rb | 90 +++++++++++++------ 1 file changed, 63 insertions(+), 27 deletions(-) diff --git a/ee/app/models/concerns/work_items/has_status.rb b/ee/app/models/concerns/work_items/has_status.rb index feddda878e1c25..0777b2a478fdbf 100644 --- a/ee/app/models/concerns/work_items/has_status.rb +++ b/ee/app/models/concerns/work_items/has_status.rb @@ -105,37 +105,73 @@ def self.generate_status_order_sql opened_state_value = Issue.available_states[:opened] closed_state_value = Issue.available_states[:closed] + mappings_exist_check = "EXISTS ( + SELECT 1 FROM work_item_custom_status_mappings WHERE namespace_id = namespaces.traversal_ids[1] + )" + + custom_status_has_mapping_check = "EXISTS ( + SELECT 1 FROM work_item_custom_status_mappings + WHERE old_status_id = work_item_current_statuses.custom_status_id + AND work_item_type_id = #{table_name}.work_item_type_id + AND namespace_id = namespaces.traversal_ids[1] + AND (valid_from IS NULL OR valid_from <= work_item_current_statuses.updated_at) + AND (valid_until IS NULL OR valid_until > work_item_current_statuses.updated_at) + )" + + system_status_has_mapping_check = "EXISTS ( + SELECT 1 FROM work_item_custom_statuses inner_converted_statuses + JOIN work_item_custom_status_mappings ON + work_item_custom_status_mappings.old_status_id = inner_converted_statuses.id + WHERE + inner_converted_statuses.converted_from_system_defined_status_identifier = + work_item_current_statuses.system_defined_status_id + AND inner_converted_statuses.namespace_id = namespaces.traversal_ids[1] + AND work_item_custom_status_mappings.work_item_type_id = #{table_name}.work_item_type_id + AND work_item_custom_status_mappings.namespace_id = + namespaces.traversal_ids[1] + AND (work_item_custom_status_mappings.valid_from IS NULL OR + work_item_custom_status_mappings.valid_from <= work_item_current_statuses.updated_at) + AND (work_item_custom_status_mappings.valid_until IS NULL OR + work_item_custom_status_mappings.valid_until > work_item_current_statuses.updated_at) + )" + <<-SQL.squish CASE WHEN work_item_current_statuses.custom_status_id IS NOT NULL THEN - COALESCE( - (SELECT mapped_statuses.category - FROM work_item_custom_status_mappings - JOIN work_item_custom_statuses mapped_statuses ON mapped_statuses.id = work_item_custom_status_mappings.new_status_id - WHERE work_item_custom_status_mappings.old_status_id = work_item_current_statuses.custom_status_id - AND work_item_custom_status_mappings.work_item_type_id = #{table_name}.work_item_type_id - AND work_item_custom_status_mappings.namespace_id = namespaces.traversal_ids[1] - AND (work_item_custom_status_mappings.valid_from IS NULL OR work_item_custom_status_mappings.valid_from <= work_item_current_statuses.updated_at) - AND (work_item_custom_status_mappings.valid_until IS NULL OR work_item_custom_status_mappings.valid_until > work_item_current_statuses.updated_at) - LIMIT 1), - work_item_custom_statuses.category - ) + CASE + WHEN #{mappings_exist_check} AND #{custom_status_has_mapping_check} THEN + (SELECT mapped_statuses.category + FROM work_item_custom_status_mappings + JOIN work_item_custom_statuses mapped_statuses ON mapped_statuses.id = work_item_custom_status_mappings.new_status_id + WHERE work_item_custom_status_mappings.old_status_id = work_item_current_statuses.custom_status_id + AND work_item_custom_status_mappings.work_item_type_id = #{table_name}.work_item_type_id + AND work_item_custom_status_mappings.namespace_id = namespaces.traversal_ids[1] + AND (work_item_custom_status_mappings.valid_from IS NULL OR work_item_custom_status_mappings.valid_from <= work_item_current_statuses.updated_at) + AND (work_item_custom_status_mappings.valid_until IS NULL OR work_item_custom_status_mappings.valid_until > work_item_current_statuses.updated_at) + LIMIT 1) + ELSE + work_item_custom_statuses.category + END WHEN work_item_current_statuses.system_defined_status_id IS NOT NULL THEN - COALESCE( - (SELECT mapped_statuses.category - FROM work_item_custom_statuses inner_converted_statuses - JOIN work_item_custom_status_mappings ON work_item_custom_status_mappings.old_status_id = inner_converted_statuses.id - JOIN work_item_custom_statuses mapped_statuses ON mapped_statuses.id = work_item_custom_status_mappings.new_status_id - WHERE inner_converted_statuses.converted_from_system_defined_status_identifier = work_item_current_statuses.system_defined_status_id - AND inner_converted_statuses.namespace_id = namespaces.traversal_ids[1] - AND work_item_custom_status_mappings.work_item_type_id = #{table_name}.work_item_type_id - AND work_item_custom_status_mappings.namespace_id = namespaces.traversal_ids[1] - AND (work_item_custom_status_mappings.valid_from IS NULL OR work_item_custom_status_mappings.valid_from <= work_item_current_statuses.updated_at) - AND (work_item_custom_status_mappings.valid_until IS NULL OR work_item_custom_status_mappings.valid_until > work_item_current_statuses.updated_at) - LIMIT 1), - converted_statuses.category, - CASE work_item_current_statuses.system_defined_status_id #{system_defined_status_cases} END - ) + CASE + WHEN #{mappings_exist_check} AND #{system_status_has_mapping_check} THEN + (SELECT mapped_statuses.category + FROM work_item_custom_statuses inner_converted_statuses + JOIN work_item_custom_status_mappings ON work_item_custom_status_mappings.old_status_id = inner_converted_statuses.id + JOIN work_item_custom_statuses mapped_statuses ON mapped_statuses.id = work_item_custom_status_mappings.new_status_id + WHERE inner_converted_statuses.converted_from_system_defined_status_identifier = work_item_current_statuses.system_defined_status_id + AND inner_converted_statuses.namespace_id = namespaces.traversal_ids[1] + AND work_item_custom_status_mappings.work_item_type_id = #{table_name}.work_item_type_id + AND work_item_custom_status_mappings.namespace_id = namespaces.traversal_ids[1] + AND (work_item_custom_status_mappings.valid_from IS NULL OR work_item_custom_status_mappings.valid_from <= work_item_current_statuses.updated_at) + AND (work_item_custom_status_mappings.valid_until IS NULL OR work_item_custom_status_mappings.valid_until > work_item_current_statuses.updated_at) + LIMIT 1) + ELSE + COALESCE( + converted_statuses.category, + CASE work_item_current_statuses.system_defined_status_id #{system_defined_status_cases} END + ) + END ELSE CASE WHEN #{table_name}.state_id = #{opened_state_value} THEN #{default_open_sort_order} -- GitLab