Avoid n+1 queries on projects/portfolio lists

Open ulferts opened this issue 3 months ago • 0 comments

Ticket

https://community.openproject.org/wp/69685

What are you trying to accomplish?

Replace n+1 queries when fetching the project/portfolio index filters:

  ProjectCustomField Exists? (0.1ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2 LIMIT $3  [["type", "ProjectCustomField"], ["id", 606], ["LIMIT", 1]]
  ↳ app/models/queries/filters/shared/custom_fields/base.rb:56:in 'Queries::Filters::Shared::CustomFields::Base#available?'
  ProjectCustomField Exists? (0.2ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2 LIMIT $3  [["type", "ProjectCustomField"], ["id", 607], ["LIMIT", 1]]
  ↳ app/models/queries/filters/shared/custom_fields/base.rb:56:in 'Queries::Filters::Shared::CustomFields::Base#available?'
  ProjectCustomField Exists? (0.2ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2 LIMIT $3  [["type", "ProjectCustomField"], ["id", 608], ["LIMIT", 1]]
  ↳ app/models/queries/filters/shared/custom_fields/base.rb:56:in 'Queries::Filters::Shared::CustomFields::Base#available?'
  ProjectCustomField Exists? (0.5ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2 LIMIT $3  [["type", "ProjectCustomField"], ["id", 609], ["LIMIT", 1]]
  ↳ app/models/queries/filters/shared/custom_fields/base.rb:56:in 'Queries::Filters::Shared::CustomFields::Base#available?'
  ProjectCustomField Exists? (0.2ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2 LIMIT $3  [["type", "ProjectCustomField"], ["id", 610], ["LIMIT", 1]]
  ↳ app/models/queries/filters/shared/custom_fields/base.rb:56:in 'Queries::Filters::Shared::CustomFields::Base#available?'
  ProjectCustomField Exists? (0.1ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2 LIMIT $3  [["type", "ProjectCustomField"], ["id", 611], ["LIMIT", 1]]
  ↳ app/models/queries/filters/shared/custom_fields/base.rb:56:in 'Queries::Filters::Shared::CustomFields::Base#available?'
  ProjectCustomField Exists? (0.1ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2 LIMIT $3  [["type", "ProjectCustomField"], ["id", 612], ["LIMIT", 1]]
  ↳ app/models/queries/filters/shared/custom_fields/base.rb:56:in 'Queries::Filters::Shared::CustomFields::Base#available?'
  ProjectCustomField Exists? (0.1ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2 LIMIT $3  [["type", "ProjectCustomField"], ["id", 613], ["LIMIT", 1]]
  ↳ app/models/queries/filters/shared/custom_fields/base.rb:56:in 'Queries::Filters::Shared::CustomFields::Base#available?'
  ProjectCustomField Exists? (0.1ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2 LIMIT $3  [["type", "ProjectCustomField"], ["id", 614], ["LIMIT", 1]]
  ↳ app/models/queries/filters/shared/custom_fields/base.rb:56:in 'Queries::Filters::Shared::CustomFields::Base#available?'
  ProjectCustomField Exists? (0.1ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2 LIMIT $3  [["type", "ProjectCustomField"], ["id", 615], ["LIMIT", 1]]
  ↳ app/models/queries/filters/shared/custom_fields/base.rb:56:in 'Queries::Filters::Shared::CustomFields::Base#available?'
  ProjectCustomField Exists? (0.1ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2 LIMIT $3  [["type", "ProjectCustomField"], ["id", 616], ["LIMIT", 1]]
  ↳ app/models/queries/filters/shared/custom_fields/base.rb:56:in 'Queries::Filters::Shared::CustomFields::Base#available?'
  ProjectCustomField Exists? (0.1ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2 LIMIT $3  [["type", "ProjectCustomField"], ["id", 617], ["LIMIT", 1]]
  ↳ app/models/queries/filters/shared/custom_fields/base.rb:56:in 'Queries::Filters::Shared::CustomFields::Base#available?'
  ProjectCustomField Exists? (0.1ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2 LIMIT $3  [["type", "ProjectCustomField"], ["id", 618], ["LIMIT", 1]]
  ↳ app/models/queries/filters/shared/custom_fields/base.rb:56:in 'Queries::Filters::Shared::CustomFields::Base#available?'
  ProjectCustomField Exists? (0.1ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2 LIMIT $3  [["type", "ProjectCustomField"], ["id", 619], ["LIMIT", 1]]
  ↳ app/models/queries/filters/shared/custom_fields/base.rb:56:in 'Queries::Filters::Shared::CustomFields::Base#available?'
  ProjectCustomField Exists? (0.1ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2 LIMIT $3  [["type", "ProjectCustomField"], ["id", 620], ["LIMIT", 1]]
  ↳ app/models/queries/filters/shared/custom_fields/base.rb:56:in 'Queries::Filters::Shared::CustomFields::Base#available?'
  ProjectCustomField Exists? (0.1ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2 LIMIT $3  [["type", "ProjectCustomField"], ["id", 621], ["LIMIT", 1]]
  ↳ app/models/queries/filters/shared/custom_fields/base.rb:56:in 'Queries::Filters::Shared::CustomFields::Base#available?'
  ProjectCustomField Exists? (0.1ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2 LIMIT $3  [["type", "ProjectCustomField"], ["id", 622], ["LIMIT", 1]]
  ↳ app/models/queries/filters/shared/custom_fields/base.rb:56:in 'Queries::Filters::Shared::CustomFields::Base#available?'
  ProjectCustomField Exists? (0.1ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2 LIMIT $3  [["type", "ProjectCustomField"], ["id", 623], ["LIMIT", 1]]
  ↳ app/models/queries/filters/shared/custom_fields/base.rb:56:in 'Queries::Filters::Shared::CustomFields::Base#available?'
  ProjectCustomField Exists? (0.2ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2 LIMIT $3  [["type", "ProjectCustomField"], ["id", 654], ["LIMIT", 1]]
  ↳ app/models/queries/filters/shared/custom_fields/base.rb:56:in 'Queries::Filters::Shared::CustomFields::Base#available?'
  ProjectCustomField Exists? (0.8ms)  SELECT 1 AS one FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2 LIMIT $3  [["type", "ProjectCustomField"], ["id", 653], ["LIMIT", 1]]

and

ProjectCustomField Load (0.6ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."type" = $1  [["type", "ProjectCustomField"]]
↳ app/models/queries/projects/filters/custom_field_context.rb:42:in 'Queries::Projects::Filters::CustomFieldContext.custom_fields'
CACHE ProjectCustomField Load (0.0ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."type" = $1  [["type", "ProjectCustomField"]]
↳ app/models/queries/projects/filters/custom_field_context.rb:42:in 'Queries::Projects::Filters::CustomFieldContext.custom_fields'
CACHE ProjectCustomField Load (0.0ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."type" = $1  [["type", "ProjectCustomField"]]
↳ app/models/queries/projects/filters/custom_field_context.rb:42:in 'Queries::Projects::Filters::CustomFieldContext.custom_fields'
CACHE ProjectCustomField Load (0.0ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."type" = $1  [["type", "ProjectCustomField"]]
↳ app/models/queries/projects/filters/custom_field_context.rb:42:in 'Queries::Projects::Filters::CustomFieldContext.custom_fields'
CACHE ProjectCustomField Load (0.0ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."type" = $1  [["type", "ProjectCustomField"]]
↳ app/models/queries/projects/filters/custom_field_context.rb:42:in 'Queries::Projects::Filters::CustomFieldContext.custom_fields'
CACHE ProjectCustomField Load (0.0ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."type" = $1  [["type", "ProjectCustomField"]]
↳ app/models/queries/projects/filters/custom_field_context.rb:42:in 'Queries::Projects::Filters::CustomFieldContext.custom_fields'
CACHE ProjectCustomField Load (0.0ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."type" = $1  [["type", "ProjectCustomField"]]
↳ app/models/queries/projects/filters/custom_field_context.rb:42:in 'Queries::Projects::Filters::CustomFieldContext.custom_fields'
CACHE ProjectCustomField Load (0.0ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."type" = $1  [["type", "ProjectCustomField"]]
↳ app/models/queries/projects/filters/custom_field_context.rb:42:in 'Queries::Projects::Filters::CustomFieldContext.custom_fields'
CACHE ProjectCustomField Load (0.0ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."type" = $1  [["type", "ProjectCustomField"]]
↳ app/models/queries/projects/filters/custom_field_context.rb:42:in 'Queries::Projects::Filters::CustomFieldContext.custom_fields'
CACHE ProjectCustomField Load (0.0ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."type" = $1  [["type", "ProjectCustomField"]]
↳ app/models/queries/projects/filters/custom_field_context.rb:42:in 'Queries::Projects::Filters::CustomFieldContext.custom_fields'

On the project list itself, it removes the following n+1 queries:

  Project Exists? (0.4ms)  SELECT 1 AS one FROM "projects" WHERE "projects"."id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."active" = TRUE) LIMIT $1  [["LIMIT", 1]]
  ↳ app/services/authorization/user_permissible_service.rb:100:in 'block in Authorization::UserPermissibleService#cached_in_any_project?'
  ProjectCustomField Load (0.2ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2  [["type", "ProjectCustomField"], ["id", 661]]
  ↳ app/models/queries/projects/custom_field_context.rb:57:in 'Queries::Projects::CustomFieldContext.preload_custom_fields'
  CalculatedValueError Load (1.5ms)  SELECT "calculated_value_errors".* FROM "calculated_value_errors" WHERE "calculated_value_errors"."custom_field_id" = $1  [["custom_field_id", 661]]
  ↳ app/models/queries/projects/custom_field_context.rb:57:in 'Queries::Projects::CustomFieldContext.preload_custom_fields'
  ProjectCustomField Load (0.2ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2  [["type", "ProjectCustomField"], ["id", 675]]
  ↳ app/models/queries/projects/custom_field_context.rb:57:in 'Queries::Projects::CustomFieldContext.preload_custom_fields'
  CalculatedValueError Load (0.3ms)  SELECT "calculated_value_errors".* FROM "calculated_value_errors" WHERE "calculated_value_errors"."custom_field_id" = $1  [["custom_field_id", 675]]
  ↳ app/models/queries/projects/custom_field_context.rb:57:in 'Queries::Projects::CustomFieldContext.preload_custom_fields'
  ProjectCustomField Load (0.2ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2  [["type", "ProjectCustomField"], ["id", 676]]
  ↳ app/models/queries/projects/custom_field_context.rb:57:in 'Queries::Projects::CustomFieldContext.preload_custom_fields'
  CalculatedValueError Load (0.2ms)  SELECT "calculated_value_errors".* FROM "calculated_value_errors" WHERE "calculated_value_errors"."custom_field_id" = $1  [["custom_field_id", 676]]
  ↳ app/models/queries/projects/custom_field_context.rb:57:in 'Queries::Projects::CustomFieldContext.preload_custom_fields'
  ProjectCustomField Load (0.2ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2  [["type", "ProjectCustomField"], ["id", 677]]
  ↳ app/models/queries/projects/custom_field_context.rb:57:in 'Queries::Projects::CustomFieldContext.preload_custom_fields'
  CalculatedValueError Load (0.3ms)  SELECT "calculated_value_errors".* FROM "calculated_value_errors" WHERE "calculated_value_errors"."custom_field_id" = $1  [["custom_field_id", 677]]
  ↳ app/models/queries/projects/custom_field_context.rb:57:in 'Queries::Projects::CustomFieldContext.preload_custom_fields'
  ProjectCustomField Load (0.2ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2  [["type", "ProjectCustomField"], ["id", 678]]
  ↳ app/models/queries/projects/custom_field_context.rb:57:in 'Queries::Projects::CustomFieldContext.preload_custom_fields'
  CalculatedValueError Load (0.2ms)  SELECT "calculated_value_errors".* FROM "calculated_value_errors" WHERE "calculated_value_errors"."custom_field_id" = $1  [["custom_field_id", 678]]
  ↳ app/models/queries/projects/custom_field_context.rb:57:in 'Queries::Projects::CustomFieldContext.preload_custom_fields'
  ProjectCustomField Load (0.4ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."type" = $1 AND "custom_fields"."id" = $2  [["type", "ProjectCustomField"], ["id", 679]]
  ↳ app/models/queries/projects/custom_field_context.rb:57:in 'Queries::Projects::CustomFieldContext.preload_custom_fields'
  CalculatedValueError Load (0.3ms)  SELECT "calculated_value_errors".* FROM "calculated_value_errors" WHERE "calculated_value_errors"."custom_field_id" = $1  [["custom_field_id", 679]]
  ↳ app/models/queries/projects/custom_field_context.rb:57:in 'Queries::Projects::CustomFieldContext.preload_custom_fields'

and those in case custom field columns are selected:

  CACHE SQL (0.0ms)  SELECT "custom_fields"."id" AS t0_r0, "custom_fields"."type" AS t0_r1, "custom_fields"."field_format" AS t0_r2, "custom_fields"."regexp" AS t0_r3, "custom_fields"."min_length" AS t0_r4, "custom_fields"."max_length" AS t0_r5, "custom_fields"."is_required" AS t0_r6, "custom_fields"."is_for_all" AS t0_r7, "custom_fields"."is_filter" AS t0_r8, "custom_fields"."position" AS t0_r9, "custom_fields"."searchable" AS t0_r10, "custom_fields"."editable" AS t0_r11, "custom_fields"."admin_only" AS t0_r12, "custom_fields"."multi_value" AS t0_r13, "custom_fields"."default_value" AS t0_r14, "custom_fields"."name" AS t0_r15, "custom_fields"."created_at" AS t0_r16, "custom_fields"."updated_at" AS t0_r17, "custom_fields"."content_right_to_left" AS t0_r18, "custom_fields"."allow_non_open_versions" AS t0_r19, "custom_fields"."custom_field_section_id" AS t0_r20, "custom_fields"."position_in_custom_field_section" AS t0_r21, "custom_fields"."formula" AS t0_r22, "custom_field_sections"."id" AS t1_r0, "custom_field_sections"."position" AS t1_r1, "custom_field_sections"."name" AS t1_r2, "custom_field_sections"."type" AS t1_r3, "custom_field_sections"."created_at" AS t1_r4, "custom_field_sections"."updated_at" AS t1_r5, "custom_field_sections"."display_representation" AS t1_r6 FROM "custom_fields" LEFT OUTER JOIN "custom_field_sections" ON "custom_field_sections"."id" = "custom_fields"."custom_field_section_id" AND "custom_field_sections"."type" = $1 WHERE "custom_fields"."type" = $2 ORDER BY custom_field_sections.position, "custom_fields"."position_in_custom_field_section" ASC  [["type", "ProjectCustomFieldSection"], ["type", "ProjectCustomField"]]
  ↳ lib_static/plugins/acts_as_customizable/lib/acts_as_customizable.rb:345:in 'Enumerable#flat_map'
  SQL (1.0ms)  SELECT "custom_fields"."id" AS t0_r0, "custom_fields"."type" AS t0_r1, "custom_fields"."field_format" AS t0_r2, "custom_fields"."regexp" AS t0_r3, "custom_fields"."min_length" AS t0_r4, "custom_fields"."max_length" AS t0_r5, "custom_fields"."is_required" AS t0_r6, "custom_fields"."is_for_all" AS t0_r7, "custom_fields"."is_filter" AS t0_r8, "custom_fields"."position" AS t0_r9, "custom_fields"."searchable" AS t0_r10, "custom_fields"."editable" AS t0_r11, "custom_fields"."admin_only" AS t0_r12, "custom_fields"."multi_value" AS t0_r13, "custom_fields"."default_value" AS t0_r14, "custom_fields"."name" AS t0_r15, "custom_fields"."created_at" AS t0_r16, "custom_fields"."updated_at" AS t0_r17, "custom_fields"."content_right_to_left" AS t0_r18, "custom_fields"."allow_non_open_versions" AS t0_r19, "custom_fields"."custom_field_section_id" AS t0_r20, "custom_fields"."position_in_custom_field_section" AS t0_r21, "custom_fields"."formula" AS t0_r22, "custom_field_sections"."id" AS t1_r0, "custom_field_sections"."position" AS t1_r1, "custom_field_sections"."name" AS t1_r2, "custom_field_sections"."type" AS t1_r3, "custom_field_sections"."created_at" AS t1_r4, "custom_field_sections"."updated_at" AS t1_r5, "custom_field_sections"."display_representation" AS t1_r6 FROM "custom_fields" LEFT OUTER JOIN "custom_field_sections" ON "custom_field_sections"."id" = "custom_fields"."custom_field_section_id" AND "custom_field_sections"."type" = $1 WHERE "custom_fields"."type" = $2 AND "custom_fields"."id" IN (SELECT "project_custom_field_project_mappings"."custom_field_id" FROM "project_custom_field_project_mappings" WHERE "project_custom_field_project_mappings"."project_id" = $3) ORDER BY custom_field_sections.position, "custom_fields"."position_in_custom_field_section" ASC  [["type", "ProjectCustomFieldSection"], ["type", "ProjectCustomField"], ["project_id", 4]]
  ↳ lib_static/plugins/acts_as_customizable/lib/acts_as_customizable.rb:345:in 'Enumerable#flat_map'
  CACHE SQL (0.0ms)  SELECT "custom_fields"."id" AS t0_r0, "custom_fields"."type" AS t0_r1, "custom_fields"."field_format" AS t0_r2, "custom_fields"."regexp" AS t0_r3, "custom_fields"."min_length" AS t0_r4, "custom_fields"."max_length" AS t0_r5, "custom_fields"."is_required" AS t0_r6, "custom_fields"."is_for_all" AS t0_r7, "custom_fields"."is_filter" AS t0_r8, "custom_fields"."position" AS t0_r9, "custom_fields"."searchable" AS t0_r10, "custom_fields"."editable" AS t0_r11, "custom_fields"."admin_only" AS t0_r12, "custom_fields"."multi_value" AS t0_r13, "custom_fields"."default_value" AS t0_r14, "custom_fields"."name" AS t0_r15, "custom_fields"."created_at" AS t0_r16, "custom_fields"."updated_at" AS t0_r17, "custom_fields"."content_right_to_left" AS t0_r18, "custom_fields"."allow_non_open_versions" AS t0_r19, "custom_fields"."custom_field_section_id" AS t0_r20, "custom_fields"."position_in_custom_field_section" AS t0_r21, "custom_fields"."formula" AS t0_r22, "custom_field_sections"."id" AS t1_r0, "custom_field_sections"."position" AS t1_r1, "custom_field_sections"."name" AS t1_r2, "custom_field_sections"."type" AS t1_r3, "custom_field_sections"."created_at" AS t1_r4, "custom_field_sections"."updated_at" AS t1_r5, "custom_field_sections"."display_representation" AS t1_r6 FROM "custom_fields" LEFT OUTER JOIN "custom_field_sections" ON "custom_field_sections"."id" = "custom_fields"."custom_field_section_id" AND "custom_field_sections"."type" = $1 WHERE "custom_fields"."type" = $2 ORDER BY custom_field_sections.position, "custom_fields"."position_in_custom_field_section" ASC  [["type", "ProjectCustomFieldSection"], ["type", "ProjectCustomField"]]
  ↳ lib_static/plugins/acts_as_customizable/lib/acts_as_customizable.rb:345:in 'Enumerable#flat_map'
  SQL (1.2ms)  SELECT "custom_fields"."id" AS t0_r0, "custom_fields"."type" AS t0_r1, "custom_fields"."field_format" AS t0_r2, "custom_fields"."regexp" AS t0_r3, "custom_fields"."min_length" AS t0_r4, "custom_fields"."max_length" AS t0_r5, "custom_fields"."is_required" AS t0_r6, "custom_fields"."is_for_all" AS t0_r7, "custom_fields"."is_filter" AS t0_r8, "custom_fields"."position" AS t0_r9, "custom_fields"."searchable" AS t0_r10, "custom_fields"."editable" AS t0_r11, "custom_fields"."admin_only" AS t0_r12, "custom_fields"."multi_value" AS t0_r13, "custom_fields"."default_value" AS t0_r14, "custom_fields"."name" AS t0_r15, "custom_fields"."created_at" AS t0_r16, "custom_fields"."updated_at" AS t0_r17, "custom_fields"."content_right_to_left" AS t0_r18, "custom_fields"."allow_non_open_versions" AS t0_r19, "custom_fields"."custom_field_section_id" AS t0_r20, "custom_fields"."position_in_custom_field_section" AS t0_r21, "custom_fields"."formula" AS t0_r22, "custom_field_sections"."id" AS t1_r0, "custom_field_sections"."position" AS t1_r1, "custom_field_sections"."name" AS t1_r2, "custom_field_sections"."type" AS t1_r3, "custom_field_sections"."created_at" AS t1_r4, "custom_field_sections"."updated_at" AS t1_r5, "custom_field_sections"."display_representation" AS t1_r6 FROM "custom_fields" LEFT OUTER JOIN "custom_field_sections" ON "custom_field_sections"."id" = "custom_fields"."custom_field_section_id" AND "custom_field_sections"."type" = $1 WHERE "custom_fields"."type" = $2 AND "custom_fields"."id" IN (SELECT "project_custom_field_project_mappings"."custom_field_id" FROM "project_custom_field_project_mappings" WHERE "project_custom_field_project_mappings"."project_id" = $3) ORDER BY custom_field_sections.position, "custom_fields"."position_in_custom_field_section" ASC  [["type", "ProjectCustomFieldSection"], ["type", "ProjectCustomField"], ["project_id", 5]]
  ↳ lib_static/plugins/acts_as_customizable/lib/acts_as_customizable.rb:345:in 'Enumerable#flat_map'
  CACHE CustomField Load (0.0ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."id" = $1 LIMIT $2  [["id", 517], ["LIMIT", 1]]
  ↳ app/helpers/calculated_values/errors_helper.rb:54:in 'CalculatedValues::ErrorsHelper#calculated_value_error_msg'
  CACHE SQL (0.0ms)  SELECT "custom_fields"."id" AS t0_r0, "custom_fields"."type" AS t0_r1, "custom_fields"."field_format" AS t0_r2, "custom_fields"."regexp" AS t0_r3, "custom_fields"."min_length" AS t0_r4, "custom_fields"."max_length" AS t0_r5, "custom_fields"."is_required" AS t0_r6, "custom_fields"."is_for_all" AS t0_r7, "custom_fields"."is_filter" AS t0_r8, "custom_fields"."position" AS t0_r9, "custom_fields"."searchable" AS t0_r10, "custom_fields"."editable" AS t0_r11, "custom_fields"."admin_only" AS t0_r12, "custom_fields"."multi_value" AS t0_r13, "custom_fields"."default_value" AS t0_r14, "custom_fields"."name" AS t0_r15, "custom_fields"."created_at" AS t0_r16, "custom_fields"."updated_at" AS t0_r17, "custom_fields"."content_right_to_left" AS t0_r18, "custom_fields"."allow_non_open_versions" AS t0_r19, "custom_fields"."custom_field_section_id" AS t0_r20, "custom_fields"."position_in_custom_field_section" AS t0_r21, "custom_fields"."formula" AS t0_r22, "custom_field_sections"."id" AS t1_r0, "custom_field_sections"."position" AS t1_r1, "custom_field_sections"."name" AS t1_r2, "custom_field_sections"."type" AS t1_r3, "custom_field_sections"."created_at" AS t1_r4, "custom_field_sections"."updated_at" AS t1_r5, "custom_field_sections"."display_representation" AS t1_r6 FROM "custom_fields" LEFT OUTER JOIN "custom_field_sections" ON "custom_field_sections"."id" = "custom_fields"."custom_field_section_id" AND "custom_field_sections"."type" = $1 WHERE "custom_fields"."type" = $2 ORDER BY custom_field_sections.position, "custom_fields"."position_in_custom_field_section" ASC  [["type", "ProjectCustomFieldSection"], ["type", "ProjectCustomField"]]
  ↳ lib_static/plugins/acts_as_customizable/lib/acts_as_customizable.rb:345:in 'Enumerable#flat_map'
  SQL (1.0ms)  SELECT "custom_fields"."id" AS t0_r0, "custom_fields"."type" AS t0_r1, "custom_fields"."field_format" AS t0_r2, "custom_fields"."regexp" AS t0_r3, "custom_fields"."min_length" AS t0_r4, "custom_fields"."max_length" AS t0_r5, "custom_fields"."is_required" AS t0_r6, "custom_fields"."is_for_all" AS t0_r7, "custom_fields"."is_filter" AS t0_r8, "custom_fields"."position" AS t0_r9, "custom_fields"."searchable" AS t0_r10, "custom_fields"."editable" AS t0_r11, "custom_fields"."admin_only" AS t0_r12, "custom_fields"."multi_value" AS t0_r13, "custom_fields"."default_value" AS t0_r14, "custom_fields"."name" AS t0_r15, "custom_fields"."created_at" AS t0_r16, "custom_fields"."updated_at" AS t0_r17, "custom_fields"."content_right_to_left" AS t0_r18, "custom_fields"."allow_non_open_versions" AS t0_r19, "custom_fields"."custom_field_section_id" AS t0_r20, "custom_fields"."position_in_custom_field_section" AS t0_r21, "custom_fields"."formula" AS t0_r22, "custom_field_sections"."id" AS t1_r0, "custom_field_sections"."position" AS t1_r1, "custom_field_sections"."name" AS t1_r2, "custom_field_sections"."type" AS t1_r3, "custom_field_sections"."created_at" AS t1_r4, "custom_field_sections"."updated_at" AS t1_r5, "custom_field_sections"."display_representation" AS t1_r6 FROM "custom_fields" LEFT OUTER JOIN "custom_field_sections" ON "custom_field_sections"."id" = "custom_fields"."custom_field_section_id" AND "custom_field_sections"."type" = $1 WHERE "custom_fields"."type" = $2 AND "custom_fields"."id" IN (SELECT "project_custom_field_project_mappings"."custom_field_id" FROM "project_custom_field_project_mappings" WHERE "project_custom_field_project_mappings"."project_id" = $3) ORDER BY custom_field_sections.position, "custom_fields"."position_in_custom_field_section" ASC  [["type", "ProjectCustomFieldSection"], ["type", "ProjectCustomField"], ["project_id", 8]]
  ↳ lib_static/plugins/acts_as_customizable/lib/acts_as_customizable.rb:345:in 'Enumerable#flat_map'
  CACHE CustomField Load (0.0ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."id" = $1 LIMIT $2  [["id", 517], ["LIMIT", 1]]
  ↳ app/helpers/calculated_values/errors_helper.rb:54:in 'CalculatedValues::ErrorsHelper#calculated_value_error_msg'
  CACHE SQL (0.0ms)  SELECT "custom_fields"."id" AS t0_r0, "custom_fields"."type" AS t0_r1, "custom_fields"."field_format" AS t0_r2, "custom_fields"."regexp" AS t0_r3, "custom_fields"."min_length" AS t0_r4, "custom_fields"."max_length" AS t0_r5, "custom_fields"."is_required" AS t0_r6, "custom_fields"."is_for_all" AS t0_r7, "custom_fields"."is_filter" AS t0_r8, "custom_fields"."position" AS t0_r9, "custom_fields"."searchable" AS t0_r10, "custom_fields"."editable" AS t0_r11, "custom_fields"."admin_only" AS t0_r12, "custom_fields"."multi_value" AS t0_r13, "custom_fields"."default_value" AS t0_r14, "custom_fields"."name" AS t0_r15, "custom_fields"."created_at" AS t0_r16, "custom_fields"."updated_at" AS t0_r17, "custom_fields"."content_right_to_left" AS t0_r18, "custom_fields"."allow_non_open_versions" AS t0_r19, "custom_fields"."custom_field_section_id" AS t0_r20, "custom_fields"."position_in_custom_field_section" AS t0_r21, "custom_fields"."formula" AS t0_r22, "custom_field_sections"."id" AS t1_r0, "custom_field_sections"."position" AS t1_r1, "custom_field_sections"."name" AS t1_r2, "custom_field_sections"."type" AS t1_r3, "custom_field_sections"."created_at" AS t1_r4, "custom_field_sections"."updated_at" AS t1_r5, "custom_field_sections"."display_representation" AS t1_r6 FROM "custom_fields" LEFT OUTER JOIN "custom_field_sections" ON "custom_field_sections"."id" = "custom_fields"."custom_field_section_id" AND "custom_field_sections"."type" = $1 WHERE "custom_fields"."type" = $2 ORDER BY custom_field_sections.position, "custom_fields"."position_in_custom_field_section" ASC  [["type", "ProjectCustomFieldSection"], ["type", "ProjectCustomField"]]
  ↳ lib_static/plugins/acts_as_customizable/lib/acts_as_customizable.rb:345:in 'Enumerable#flat_map'
  SQL (1.1ms)  SELECT "custom_fields"."id" AS t0_r0, "custom_fields"."type" AS t0_r1, "custom_fields"."field_format" AS t0_r2, "custom_fields"."regexp" AS t0_r3, "custom_fields"."min_length" AS t0_r4, "custom_fields"."max_length" AS t0_r5, "custom_fields"."is_required" AS t0_r6, "custom_fields"."is_for_all" AS t0_r7, "custom_fields"."is_filter" AS t0_r8, "custom_fields"."position" AS t0_r9, "custom_fields"."searchable" AS t0_r10, "custom_fields"."editable" AS t0_r11, "custom_fields"."admin_only" AS t0_r12, "custom_fields"."multi_value" AS t0_r13, "custom_fields"."default_value" AS t0_r14, "custom_fields"."name" AS t0_r15, "custom_fields"."created_at" AS t0_r16, "custom_fields"."updated_at" AS t0_r17, "custom_fields"."content_right_to_left" AS t0_r18, "custom_fields"."allow_non_open_versions" AS t0_r19, "custom_fields"."custom_field_section_id" AS t0_r20, "custom_fields"."position_in_custom_field_section" AS t0_r21, "custom_fields"."formula" AS t0_r22, "custom_field_sections"."id" AS t1_r0, "custom_field_sections"."position" AS t1_r1, "custom_field_sections"."name" AS t1_r2, "custom_field_sections"."type" AS t1_r3, "custom_field_sections"."created_at" AS t1_r4, "custom_field_sections"."updated_at" AS t1_r5, "custom_field_sections"."display_representation" AS t1_r6 FROM "custom_fields" LEFT OUTER JOIN "custom_field_sections" ON "custom_field_sections"."id" = "custom_fields"."custom_field_section_id" AND "custom_field_sections"."type" = $1 WHERE "custom_fields"."type" = $2 AND "custom_fields"."id" IN (SELECT "project_custom_field_project_mappings"."custom_field_id" FROM "project_custom_field_project_mappings" WHERE "project_custom_field_project_mappings"."project_id" = $3) ORDER BY custom_field_sections.position, "custom_fields"."position_in_custom_field_section" ASC  [["type", "ProjectCustomFieldSection"], ["type", "ProjectCustomField"], ["project_id", 12]]

It does not manage to get rid of the following SQL statements:

CustomField Load (0.2ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."id" = $1 LIMIT $2  [["id", 517], ["LIMIT", 1]]
  ↳ app/helpers/calculated_values/errors_helper.rb:54:in 'CalculatedValues::ErrorsHelper#calculated_value_error_msg'
  CACHE CustomField Load (0.1ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."id" = $1 LIMIT $2  [["id", 517], ["LIMIT", 1]]
  ↳ app/helpers/calculated_values/errors_helper.rb:54:in 'CalculatedValues::ErrorsHelper#calculated_value_error_msg'
  CACHE CustomField Load (0.0ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."id" = $1 LIMIT $2  [["id", 517], ["LIMIT", 1]]
  ↳ app/helpers/calculated_values/errors_helper.rb:54:in 'CalculatedValues::ErrorsHelper#calculated_value_error_msg'
  CACHE CustomField Load (0.0ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."id" = $1 LIMIT $2  [["id", 517], ["LIMIT", 1]]
  ↳ app/helpers/calculated_values/errors_helper.rb:54:in 'CalculatedValues::ErrorsHelper#calculated_value_error_msg'
  CACHE CustomField Load (0.0ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."id" = $1 LIMIT $2  [["id", 517], ["LIMIT", 1]]
  ↳ app/helpers/calculated_values/errors_helper.rb:54:in 'CalculatedValues::ErrorsHelper#calculated_value_error_msg'
  CACHE CustomField Load (0.0ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."id" = $1 LIMIT $2  [["id", 517], ["LIMIT", 1]]
  ↳ app/helpers/calculated_values/errors_helper.rb:54:in 'CalculatedValues::ErrorsHelper#calculated_value_error_msg'
  CACHE CustomField Load (0.0ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."id" = $1 LIMIT $2  [["id", 517], ["LIMIT", 1]]
  ↳ app/helpers/calculated_values/errors_helper.rb:54:in 'CalculatedValues::ErrorsHelper#calculated_value_error_msg'
  CACHE CustomField Load (0.0ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."id" = $1 LIMIT $2  [["id", 517], ["LIMIT", 1]]
  ↳ app/helpers/calculated_values/errors_helper.rb:54:in 'CalculatedValues::ErrorsHelper#calculated_value_error_msg'
  CACHE CustomField Load (0.0ms)  SELECT "custom_fields".* FROM "custom_fields" WHERE "custom_fields"."id" = $1 LIMIT $2  [["id", 517], ["LIMIT", 1]]
  ↳ app/helpers/calculated_values/errors_helper.rb:54:in 'CalculatedValues::ErrorsHelper#calculated_value_error_msg'

in scenarios where there is a calculated cf column selected and that column showing a lot of errors. But:

Most of the time there shouldn't be that many errors.
If this is to be addressed, it would rather be by loading the error message asynchronously.

What approach did you choose and why?

By using the request store, all visible project custom fields are fetched once and then reused when the context is called from multiple places.

Dec 12 '25 14:12 ulferts