# Bug OP-17670 - Poor performance on WorkPackage.visible scope

Project: OpenProject
Status: closed
URL: https://community.openproject.org/work_packages/OP-17670

## Open points

_No work packages_

## Details

| Attribute | Value |
| --- | --- |
| Date |  |
| Priority | High |
| Module | Modules and project features / Work packages, Administration / Roles and permissions |
| Target versions | 16.3.2 |
| Observed in versions | 15.0.0 |
| Sprint | 16.3.2 |
| Backlog bucket |  |
| Bug found in version (deprecated) | 15.0.x |
| Found in mobile app version |  |
| Follow-up feedback e-mail |  |
| Requires doc change | No |
| Accessibility | No |
| Pull preview |  |
| List |  |
| Labels |  |

## People

| Attribute | Value |
| --- | --- |
| Assignee |  |
| Accountable |  |
| Product manager |  |
| Developers | Jens Ulferts |
| Designer |  |
| Ordered by |  |
| Signed off by |  |
| Interest by |  |

## Planning

| Attribute | Value |
| --- | --- |
| Relevance |  |

## Costs

| Attribute | Value |
| --- | --- |
| Votes |  |
| Work |  |
| Labor costs |  |

## Description

There are a whole lot of places where the WorkPackage.visible scope is relevant:

*   Notifictions
    
*   WorkPackage queries
    
*   Activities
    
*   ...
    

A way to improve the query executed by the scope might be like this:

```SQL
WITH
			"allowed_projects" AS (
			                    -- The Project.visible query 
								SELECT
									"projects"."id",
									members.entity_id,
									members."entity_type"
								FROM
									"members"
									INNER JOIN "projects" ON "projects"."active" = TRUE
									AND "members"."project_id" = "projects"."id"
									INNER JOIN "enabled_modules" ON "projects"."id" = "enabled_modules"."project_id"
									AND "enabled_modules"."name" IN ('work_package_tracking')
									AND "projects"."active" = TRUE
									INNER JOIN "member_roles" ON "member_roles"."member_id" = "members"."id"
									INNER JOIN "roles" ON "roles"."id" = "member_roles"."role_id"
									INNER JOIN "role_permissions" ON "roles"."id" = "role_permissions"."role_id"
									AND (
										FALSE
										OR "role_permissions"."permission" = 'view_work_packages'
										AND "enabled_modules"."name" = 'work_package_tracking'
									)
								WHERE
									"members"."user_id" = 86481
									-- Adapt the Project.visible query to have not only those without an entity but also that with
									-- the entity being 'WorkPackage'
									AND ("members"."entity_type" IS NULL OR "members"."entity_type" = 'WorkPackage')
							
							UNION ALL
							
								SELECT
									"projects"."id",
									null as entity_id,
									null as entity_type
								FROM
									"projects"
									INNER JOIN "enabled_modules" ON "projects"."id" = "enabled_modules"."project_id"
									AND "enabled_modules"."name" IN ('work_package_tracking')
									AND "projects"."active" = TRUE
									INNER JOIN "roles" ON "roles"."builtin" = 1
									AND "projects"."active"
									AND "projects"."public"
									AND NOT (
										EXISTS (
											SELECT
												1
											FROM
												"members"
											WHERE
											    -- Here, the Project.visible scope does not need to be adapted as it is 
												-- about the existence of any project role.
												"members"."user_id" = 86481
												AND "members"."entity_id" IS NULL
												AND "members"."entity_type" IS NULL
												AND "members"."project_id" = "projects"."id"
										)
									)
									INNER JOIN "role_permissions" ON "roles"."id" = "role_permissions"."role_id"
									AND (
										FALSE
										OR "role_permissions"."permission" = 'view_work_packages'
										AND "enabled_modules"."name" = 'work_package_tracking'
									)
							
						
					
			),			
			allowed_by_projects_and_work_packages AS (
              SELECT * from work_packages 
			  WHERE project_id in (SELECT id FROM allowed_projects)
			  -- Take all work packages allowed by either project wide or entity specific membership
			  -- But now remove all those that are in a project for which an entity specific membership exists (but is not for that entity)
			  -- unless there is also a project specific membership in that same project.
			  AND NOT EXISTS (
                SELECT 1 FROM allowed_projects 
				WHERE allowed_projects.id = project_id AND allowed_projects.entity_id IS NOT NULL AND allowed_projects.entity_id != id
				AND NOT EXISTS (
				  SELECT 1 FROM allowed_projects
				  WHERE allowed_projects.id = project_id AND allowed_projects.entity_id IS NULL
				)
			  )
			)

			
		SELECT
			COUNT("work_packages"."id")
		FROM
			"allowed_by_projects_and_work_packages" work_packages
```

This seems to be the quickest SQL statement that can be used, a somewhat simpler one would be

```SQL
WITH
			"allowed_projects" AS (
								SELECT
									"projects"."id",
									members.entity_id,
									members."entity_type"
								FROM
									"members"
									INNER JOIN "projects" ON "projects"."active" = TRUE
									AND "members"."project_id" = "projects"."id"
									INNER JOIN "enabled_modules" ON "projects"."id" = "enabled_modules"."project_id"
									AND "enabled_modules"."name" IN ('work_package_tracking')
									AND "projects"."active" = TRUE
									INNER JOIN "member_roles" ON "member_roles"."member_id" = "members"."id"
									INNER JOIN "roles" ON "roles"."id" = "member_roles"."role_id"
									INNER JOIN "role_permissions" ON "roles"."id" = "role_permissions"."role_id"
									AND (
										FALSE
										OR "role_permissions"."permission" = 'view_work_packages'
										AND "enabled_modules"."name" = 'work_package_tracking'
									)
								WHERE
									"members"."user_id" = 86481
									--AND "members"."entity_id" IS NULL
									AND ("members"."entity_type" IS NULL OR "members"."entity_type" = 'WorkPackage')
							
							UNION ALL
							
								SELECT
									"projects"."id",
									null as entity_id,
									null as entity_type
								FROM
									"projects"
									INNER JOIN "enabled_modules" ON "projects"."id" = "enabled_modules"."project_id"
									AND "enabled_modules"."name" IN ('work_package_tracking')
									AND "projects"."active" = TRUE
									INNER JOIN "roles" ON "roles"."builtin" = 1
									AND "projects"."active"
									AND "projects"."public"
									AND NOT (
										EXISTS (
											SELECT
												1
											FROM
												"members"
											WHERE
												"members"."user_id" = 86481
												AND "members"."entity_id" IS NULL
												AND "members"."entity_type" IS NULL
												AND "members"."project_id" = "projects"."id"
										)
									)
									INNER JOIN "role_permissions" ON "roles"."id" = "role_permissions"."role_id"
									AND (
										FALSE
										OR "role_permissions"."permission" = 'view_work_packages'
										AND "enabled_modules"."name" = 'work_package_tracking'
									)				
						
					
			),
			allowed_projects_with_project_memberships AS (
              SELECT entity_projects.*, 
			         CASE WHEN project_projects.id IS NULL
					 THEN true
					 ELSE false
					 END entity_only
			  FROM allowed_projects entity_projects
			  LEFT JOIN allowed_projects project_projects
			  ON entity_projects.id = project_projects.id AND project_projects.entity_id IS NULL
			),
			allowed_by_projects_and_work_packages AS (
              SELECT work_packages.* 
			  FROM work_packages
			  JOIN allowed_projects_with_project_memberships
			  ON project_id = allowed_projects_with_project_memberships.id AND
			  ((NOT allowed_projects_with_project_memberships.entity_only AND work_packages.project_id = allowed_projects_with_project_memberships.id) OR
			  (allowed_projects_with_project_memberships.entity_only AND work_packages.id = allowed_projects_with_project_memberships.id))
			)
		
		
		SELECT
			"work_packages"."id"
		FROM
			"allowed_by_projects_and_work_packages" work_packages
```

The query above is easier to parse but performs (slightly worse).

Both however seem simpler than what we currently have:

```SQL
WITH
			"allowed_work_packages" AS (
				SELECT
					"work_packages"."id"
				FROM
					"members"
					INNER JOIN "work_packages" ON "members"."entity_id" = "work_packages"."id"
					INNER JOIN "projects" ON "projects"."active" = TRUE
					AND "projects"."id" = "work_packages"."project_id"
					INNER JOIN "enabled_modules" ON "projects"."id" = "enabled_modules"."project_id"
					AND "enabled_modules"."name" IN ('work_package_tracking')
					AND "projects"."active" = TRUE
					INNER JOIN "member_roles" ON "member_roles"."member_id" = "members"."id"
					INNER JOIN "roles" ON "roles"."id" = "member_roles"."role_id"
					INNER JOIN "role_permissions" ON "roles"."id" = "role_permissions"."role_id"
					AND (
						FALSE
						OR "role_permissions"."permission" = 'view_work_packages'
						AND "enabled_modules"."name" = 'work_package_tracking'
					)
				WHERE
					"members"."user_id" = 86481
					AND "members"."entity_type" = 'WorkPackage'
			),
			"allowed_projects" AS (
				SELECT
					"projects"."id"
				FROM
					"projects"
				WHERE
					"projects"."id" IN (
						(
							(
								SELECT
									"projects"."id"
								FROM
									"members"
									INNER JOIN "projects" ON "projects"."active" = TRUE
									AND "members"."project_id" = "projects"."id"
									INNER JOIN "enabled_modules" ON "projects"."id" = "enabled_modules"."project_id"
									AND "enabled_modules"."name" IN ('work_package_tracking')
									AND "projects"."active" = TRUE
									INNER JOIN "member_roles" ON "member_roles"."member_id" = "members"."id"
									INNER JOIN "roles" ON "roles"."id" = "member_roles"."role_id"
									INNER JOIN "role_permissions" ON "roles"."id" = "role_permissions"."role_id"
									AND (
										FALSE
										OR "role_permissions"."permission" = 'view_work_packages'
										AND "enabled_modules"."name" = 'work_package_tracking'
									)
								WHERE
									"members"."user_id" = 86481
									AND "members"."entity_id" IS NULL
									AND "members"."entity_type" IS NULL
							)
							UNION ALL
							(
								SELECT
									"projects"."id"
								FROM
									"projects"
									INNER JOIN "enabled_modules" ON "projects"."id" = "enabled_modules"."project_id"
									AND "enabled_modules"."name" IN ('work_package_tracking')
									AND "projects"."active" = TRUE
									INNER JOIN "roles" ON "roles"."builtin" = 1
									AND "projects"."active"
									AND "projects"."public"
									AND NOT (
										EXISTS (
											SELECT
												1
											FROM
												"members"
											WHERE
												"members"."user_id" = 86481
												AND "members"."entity_id" IS NULL
												AND "members"."entity_type" IS NULL
												AND "members"."project_id" = "projects"."id"
										)
									)
									INNER JOIN "role_permissions" ON "roles"."id" = "role_permissions"."role_id"
									AND (
										FALSE
										OR "role_permissions"."permission" = 'view_work_packages'
										AND "enabled_modules"."name" = 'work_package_tracking'
									)
							)
						)
					)
			)
		SELECT
			"work_packages"."id"
		FROM
			"work_packages"
		WHERE
			(
				project_id IN (
					SELECT
						ID
					FROM
						ALLOWED_PROJECTS
				)
				OR WORK_PACKAGES.ID IN (
					SELECT
						ID
					FROM
						ALLOWED_WORK_PACKAGES
				)
			)
```
