diff --git a/ee/app/models/concerns/work_items/has_status.rb b/ee/app/models/concerns/work_items/has_status.rb index 091f9d094cefa40a6fee3389c48de3e90338b4d9..0777b2a478fdbf4686f43f6b4a8ad43b1095c885 100644 --- a/ee/app/models/concerns/work_items/has_status.rb +++ b/ee/app/models/concerns/work_items/has_status.rb @@ -105,15 +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 - 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( - 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} diff --git a/ee/spec/models/work_item_spec.rb b/ee/spec/models/work_item_spec.rb index 80923af938eece6b250e52f0a80d2cb9f1485fcf..910ad9bfb6a3620e4b5e01525452523709bbff75 100644 --- a/ee/spec/models/work_item_spec.rb +++ b/ee/spec/models/work_item_spec.rb @@ -886,6 +886,101 @@ 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(: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, + 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(: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 + + 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_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_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, + 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 [