During the development of the [equals\_with\_descendants.rb](https://github.com/opf/openproject/blob/dev/app/models/queries/operators/custom_fields/hierarchies/equals_with_descendants.rb) operator for filtering work packages a possible optimization was identified.

We seem to be duplicating a lot of joins around custom fields.

We should:

* Investigate how much of this is expected behaviour

* Investigate if there is an alternate solution

* Implement and fix this behaviour


This was identified during [a code review](https://github.com/opf/openproject/pull/17247#pullrequestreview-2462029224)

Example query

```sql
SELECT Begin of the insertion
"work_packages"."id"End of the insertion Begin of the deletion
   "work_packages"."id"End of the deletion
FROM Begin of the insertion
"work_packages"
LEFTEnd of the insertion Begin of the deletion
   "work_packages"
   LEFTEnd of the deletion OUTER JOIN "projects" ON "projects"."id" = "work_packages"."project_id"
WHERE Begin of the insertion
(
(
WORK_PACKAGES.IDEnd of the insertion Begin of the deletion
   (
       (
           WORK_PACKAGES.IDEnd of the deletion IN ( Begin of the insertion
SELECT
WORK_PACKAGES.ID
FROM
WORK_PACKAGES
LEFTEnd of the insertion Begin of the deletion
               SELECT
                   WORK_PACKAGES.ID
               FROM
                   WORK_PACKAGES
                   LEFTEnd of the deletion OUTER JOIN CUSTOM_VALUES ON CUSTOM_VALUES.CUSTOMIZED_TYPE = 'WorkPackage' Begin of the insertion
ANDEnd of the insertion Begin of the deletion
                   ANDEnd of the deletion CUSTOM_VALUES.CUSTOMIZED_ID = WORK_PACKAGES.ID Begin of the insertion
ANDEnd of the insertion Begin of the deletion
                   ANDEnd of the deletion CUSTOM_VALUES.CUSTOM_FIELD_ID = 541 Begin of the insertion
JOINEnd of the insertion Begin of the deletion
                   JOINEnd of the deletion CUSTOM_FIELDS_TYPES ON CUSTOM_FIELDS_TYPES.TYPE_ID = WORK_PACKAGES.TYPE_ID Begin of the insertion
ANDEnd of the insertion Begin of the deletion
                   ANDEnd of the deletion CUSTOM_FIELDS_TYPES.CUSTOM_FIELD_ID = 541 Begin of the insertion
JOINEnd of the insertion Begin of the deletion
                   JOINEnd of the deletion CUSTOM_FIELDS_PROJECTS ON CUSTOM_FIELDS_PROJECTS.PROJECT_ID = WORK_PACKAGES.PROJECT_ID Begin of the insertion
ANDEnd of the insertion Begin of the deletion
                   ANDEnd of the deletion CUSTOM_FIELDS_PROJECTS.CUSTOM_FIELD_ID = 541 Begin of the insertion
WHERE
(
CUSTOM_VALUES.VALUEEnd of the insertion Begin of the deletion
               WHERE
                   (
                       CUSTOM_VALUES.VALUEEnd of the deletion IS NULL Begin of the insertion
OREnd of the insertion Begin of the deletion
                       OREnd of the deletion CUSTOM_VALUES.VALUE NOT IN ('14') Begin of the insertion
)
)
)
ANDEnd of the insertion Begin of the deletion
                   )
           )
       )
       ANDEnd of the deletion (PROJECTS.ID IN (6)) Begin of the insertion
)
ANDEnd of the insertion Begin of the deletion
   )
   ANDEnd of the deletion ( Begin of the insertion
WORK_PACKAGES.IDEnd of the insertion Begin of the deletion
       WORK_PACKAGES.IDEnd of the deletion IN ( Begin of the insertion
SELECT
WORK_PACKAGES.ID
FROM
WORK_PACKAGES
LEFTEnd of the insertion Begin of the deletion
           SELECT
               WORK_PACKAGES.ID
           FROM
               WORK_PACKAGES
               LEFTEnd of the deletion OUTER JOIN CUSTOM_VALUES ON CUSTOM_VALUES.CUSTOMIZED_TYPE = 'WorkPackage' Begin of the insertion
ANDEnd of the insertion Begin of the deletion
               ANDEnd of the deletion CUSTOM_VALUES.CUSTOMIZED_ID = WORK_PACKAGES.ID Begin of the insertion
ANDEnd of the insertion Begin of the deletion
               ANDEnd of the deletion CUSTOM_VALUES.CUSTOM_FIELD_ID = 541 Begin of the insertion
JOINEnd of the insertion Begin of the deletion
               JOINEnd of the deletion CUSTOM_FIELDS_TYPES ON CUSTOM_FIELDS_TYPES.TYPE_ID = WORK_PACKAGES.TYPE_ID Begin of the insertion
ANDEnd of the insertion Begin of the deletion
               ANDEnd of the deletion CUSTOM_FIELDS_TYPES.CUSTOM_FIELD_ID = 541 Begin of the insertion
JOINEnd of the insertion Begin of the deletion
               JOINEnd of the deletion CUSTOM_FIELDS_PROJECTS ON CUSTOM_FIELDS_PROJECTS.PROJECT_ID = WORK_PACKAGES.PROJECT_ID Begin of the insertion
ANDEnd of the insertion Begin of the deletion
               ANDEnd of the deletion CUSTOM_FIELDS_PROJECTS.CUSTOM_FIELD_ID = 541 Begin of the insertion
WHERE
(
CUSTOM_VALUES.VALUEEnd of the insertion Begin of the deletion
           WHERE
               (
                   CUSTOM_VALUES.VALUEEnd of the deletion IS NULL Begin of the insertion
OREnd of the insertion Begin of the deletion
                   OREnd of the deletion CUSTOM_VALUES.VALUE NOT IN ('14') Begin of the insertion
)
)
)
ANDEnd of the insertion Begin of the deletion
               )
       )
   )
   ANDEnd of the deletion "work_packages"."id" IN ( Begin of the insertion
SELECT
"work_packages"."id"
FROM
"work_packages"
INNEREnd of the insertion Begin of the deletion
       SELECT
           "work_packages"."id"
       FROM
           "work_packages"
           INNEREnd of the deletion JOIN "projects" ON "projects"."id" = "work_packages"."project_id" Begin of the insertion
INNEREnd of the insertion Begin of the deletion
           INNEREnd of the deletion JOIN "enabled_modules" ON "projects"."id" = "enabled_modules"."project_id" Begin of the insertion
ANDEnd of the insertion Begin of the deletion
           ANDEnd of the deletion "enabled_modules"."name" IN ('work_package_tracking') Begin of the insertion
ANDEnd of the insertion Begin of the deletion
           ANDEnd of the deletion "projects"."active" = TRUE Begin of the insertion
WHERE
"projects"."active"End of the insertion Begin of the deletion
       WHERE
           "projects"."active"End of the deletion = TRUE Begin of the insertion
)End of the insertion Begin of the deletion
   )End of the deletion
ORDER BY Begin of the insertion
WORK_PACKAGES.IDEnd of the insertion Begin of the deletion
   WORK_PACKAGES.IDEnd of the deletion
```