Update by_parent filter in EpicsFinder

Related to #409703 (closed)

What does this MR do and why?

This MR updates the existing top_level_hierarchy_only filter to return epics that have parents outside the group hierarchy.

This filter is being used for the epics roadmap query to exclude child epics because they are fetched in a second query and would otherwise be duplicated.

There is an edge case where the roadmap epic might have a parent in a different group hierarchy, this epic will not be returned with the current filter and will not be included in the second query that fetched children (both queries are scope to the current group) resulting in a bug.

We can fix this behavior by widening the results in top_level_hierarchy_only to also include epics whose parents belong to other group hierarchies.

Database changes

This MR modifies the param top_level_hierarchy_only in EpicsFinder (this param has a false value by default).

SQL query

-- EpicsFinder.new(user, {group_id: group.id, top_level_hierarchy_only: true}).execute

SELECT
    epics.*
FROM
    epics
    LEFT OUTER JOIN epics parent ON parent.id = epics.parent_id
WHERE
    epics.group_id IN (
        SELECT
            namespaces.id
        FROM
            namespaces
        WHERE
            namespaces.type = 'Group'
            AND (traversal_ids @> ('{$GROUP_ID}')))
    AND (parent.group_id NOT IN (
            SELECT
                namespaces.id
            FROM
                namespaces
            WHERE
                namespaces.type = 'Group'
                AND (traversal_ids @> ('{$GROUP_ID}')))
            OR epics.parent_id IS NULL)
ORDER BY
    epics.id DESC

Query plan(internal)

Updated query plan (internal)

Additional plan in !120480 (comment 1388271245) where we test the query using all params present for the GraphQL query used for epics Roadmap.

How to set up and validate locally

1. Create a group Group A with 4 epics and Group B with one epic:

user = User.first
group_a = Group.create!(name: 'Group A', path: 'group-a')
group_b = Group.create!(name: 'Group B', path: 'group-b')

external_epic = Epic.create!(title: 'Group B Epic', group: group_b,  author: user)
epic1 = Epic.create!(title: 'Group A Epic - No Parent', group: group_a, start_date: "2023-05-01", due_date: "2023-05-31", author: user)
epic2 = Epic.create!(title: 'Group A Epic - Parent in Group B', group: group_a, start_date: "2023-05-01", due_date: "2023-05-31", author: user, parent: external_epic)
epic3 = Epic.create!(title: 'Group A Parent Epic', group: group_a, start_date: "2023-05-01", due_date: "2023-05-31", author: user)
epic4 = Epic.create!(title: 'Group A Child Epic', group: group_a, start_date: "2023-05-01", due_date: "2023-05-31", author: user, parent: epic3)

1. Visit Group A roadmap, 3 epics should be visible at the top level and Group A Child Epic should be displayed when children are extended.

Click to expand screenshots

Before	After

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.