Simplify work item retrieval in epics controller

What does this MR do and why?

Replace WorkItemsFinder call with direct epic.work_item association for better performance in EpicsController#render_as_work_item method.

When using the finder to fetch the work item, we use a complex query that can be slow (see metrics (internal)) because it includes the group's hierarchy and permissions check. With these changes, we revert to using the simpler epic query that authorises it separatly.

work item query

WITH "namespace_ids" AS MATERIALIZED (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = $13 AND "namespaces"."id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = $14 AND "namespaces"."id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = $15 AND "namespaces"."id" = $16) AND (id IN ((SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = $17 AND (traversal_ids && ARRAY(SELECT "members"."source_id" FROM "members" LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id" WHERE "members"."type" = $18 AND "members"."source_type" = $19 AND "users"."state" = $20 AND "members"."state" = $21 AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NULL AND (members.access_level > $22) AND "members"."source_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = $23 AND (traversal_ids @> ($24))) AND (access_level >= $25) AND "members"."user_id" = $26))) UNION (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = $27 AND (traversal_ids && ARRAY(SELECT "group_group_links"."shared_group_id" FROM "group_group_links" WHERE "group_group_links"."shared_group_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = $28 AND "namespaces"."id" = $29) AND (group_access >= $30) AND "group_group_links"."shared_with_group_id" IN (SELECT "members"."source_id" FROM "members" WHERE "members"."type" = $31 AND "members"."source_type" = $32 AND "members"."user_id" = $33 AND "members"."user_id" IS NOT NULL AND "members"."state" = $34 AND "members"."requested_at" IS NULL AND (members.access_level > $35)))::integer[])) UNION (SELECT unnest(traversal_ids) FROM "namespaces" WHERE "namespaces"."type" = $36 AND "namespaces"."id" IN (SELECT "projects"."namespace_id" FROM "projects" WHERE "projects"."namespace_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = $37 AND (traversal_ids @> ($38))) AND (EXISTS (SELECT $39 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = $40 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN ($41,$42)))) UNION (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = $43 AND (traversal_ids @> ($44)) AND (visibility_level > $45)))))), "accessible_sub_namespace_ids" AS MATERIALIZED (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = $46 AND "namespaces"."id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = $47 AND "namespaces"."id" = $48) AND (id IN ((SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = $49 AND (traversal_ids && ARRAY(SELECT "members"."source_id" FROM "members" LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id" WHERE "members"."type" = $50 AND "members"."source_type" = $51 AND "users"."state" = $52 AND "members"."state" = $53 AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NULL AND (members.access_level > $54) AND "members"."source_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = $55 AND (traversal_ids @> ($56))) AND (access_level >= $57) AND "members"."user_id" = $58))) UNION (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = $59 AND (traversal_ids && ARRAY(SELECT "group_group_links"."shared_group_id" FROM "group_group_links" WHERE "group_group_links"."shared_group_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = $60 AND "namespaces"."id" = $61) AND (group_access >= $62) AND "group_group_links"."shared_with_group_id" IN (SELECT "members"."source_id" FROM "members" WHERE "members"."type" = $63 AND "members"."source_type" = $64 AND "members"."user_id" = $65 AND "members"."user_id" IS NOT NULL AND "members"."state" = $66 AND "members"."requested_at" IS NULL AND (members.access_level > $67)))::integer[]))))) SELECT "issues"."id", "issues"."title", "issues"."author_id", "issues"."project_id", "issues"."created_at", "issues"."updated_at", "issues"."description", "issues"."milestone_id", "issues"."iid", "issues"."updated_by_id", "issues"."weight", "issues"."confidential", "issues"."moved_to_id", "issues"."due_date", "issues"."lock_version", "issues"."title_html", "issues"."description_html", "issues"."time_estimate", "issues"."relative_position", "issues"."service_desk_reply_to", "issues"."cached_markdown_version", "issues"."last_edited_at", "issues"."last_edited_by_id", "issues"."discussion_locked", "issues"."closed_at", "issues"."closed_by_id", "issues"."state_id", "issues"."duplicated_to_id", "issues"."promoted_to_epic_id", "issues"."health_status", "issues"."external_key", "issues"."sprint_id", "issues"."blocking_issues_count", "issues"."upvotes_count", "issues"."work_item_type_id", "issues"."namespace_id", "issues"."start_date", "issues"."imported_from" FROM "issues" INNER JOIN "work_item_types" ON "work_item_types"."id" = "issues"."work_item_type_id" WHERE (NOT EXISTS (SELECT $1 FROM "banned_users" WHERE (banned_users.user_id = (issues.author_id + $2)))) AND (issues.namespace_id IN (SELECT id FROM namespace_ids)) AND ("issues"."confidential" = $3 OR "issues"."confidential" = $4 AND ("issues"."author_id" = $5 OR EXISTS (SELECT "issue_assignees".* FROM "issue_assignees" WHERE "issue_assignees"."user_id" = $6 AND (issue_id = issues.id)) OR (EXISTS (SELECT $7 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = $8 AND (project_authorizations.project_id = issues.project_id) AND (project_authorizations.access_level >= $9))) AND "issues"."project_id" IS NOT NULL OR "issues"."project_id" IS NULL AND "issues"."namespace_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = $10 AND (id IN (SELECT id FROM accessible_sub_namespace_ids))))) AND "issues"."iid" = $11 ORDER BY "issues"."id" DESC LIMIT $12

epic query

SELECT "epics".* FROM "epics" WHERE "epics"."group_id" = $1 AND "epics"."iid" = $2 LIMIT $3

References

How to set up and validate locally

1. Visit a public group and create a confidential epic in it.
2. Impersonate a user who is not a member of the group (or has Guest role) and visit the confidential epic. Verify that it renders a 404 page.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #553185 (closed)