Get ancestors of an Epic using GraphQL
What does this MR do?
Related to #327576 (closed)
These changes add the ancestors field to EpicType allowing us to fetch all the parent epics of an epic using GraphQL.
GraphQL Query
query {
group(fullPath: "group1") {
epics {
edges {
node {
title
ancestors {
edges {
node {
title
}
}
}
}
}
}
}
}RESPONSE
{
"data": {
"group": {
"epics": {
"edges": [
{
"node": {
"title": "Epic 5",
"ancestors": {
"edges": [
{
"node": {
"title": "Parent Epic C"
}
},
{
"node": {
"title": "Parent Epic D"
}
},
]
}
}
},
{
"node": {
"title": "Epic 4",
"ancestors": {
"edges": [
{
"node": {
"title": "Parent Epic E"
}
},
{
"node": {
"title": "Parent Epic F"
}
}
]
}
}
}
}
}
}
}
}Additionally, we include the new param child_id in EpicsFinder that allows us to filter the collection of epics by the ancestors of a child epic.
This param works similarly to parent_id, although it returns the ancestors of the given epic instead of its descendants. Similarly, the presence of the include_ancestor_groups param will return results from ancestor groups as well.
Example
Given the following epics:
parent-group/ancestor-epicparent-group/subgroup/parent-epicparent-group/subgroup/epic
With the following hierarchy:
Epic -> Parent Epic -> Ancestor Epic
When using the finder with include_ancestor_groups set as true the result will include epics from "Parent Group"
EpicsFinder.new(current_user, group_id: subgroup.id, child_id: epic.id, include_ancestor_groups: true).execute
=> "Parent Epic", "Ancestor Epic"If include_ancestor_groups is omitted its default values is false
EpicsFinder.new(current_user, group_id: subgroup.id, child_id: epic.id).execute
=> "Parent Epic"Query Plans
1. Finder query
SQL query
SELECT epics.* FROM epics WHERE epics.group_id IN (WITH RECURSIVE base_and_descendants AS ((SELECT namespaces.* FROM namespaces WHERE namespaces.type = 'Group' AND namespaces.id = 2593421)
UNION
(SELECT namespaces.* FROM namespaces, base_and_descendants WHERE namespaces.type = 'Group' AND namespaces.parent_id = base_and_descendants.id)) SELECT namespaces.id FROM base_and_descendants AS namespaces) AND epics.id IN (WITH RECURSIVE base_and_ancestors AS ((SELECT 1 as depth, ARRAY[epics.id] AS tree_path, false AS tree_cycle, epics.* FROM epics WHERE epics.id = 277866)
UNION
(SELECT (base_and_ancestors.depth + 1), tree_path || epics.id, epics.id = ANY(tree_path), epics.* FROM epics, base_and_ancestors WHERE epics.id = base_and_ancestors.parent_id AND base_and_ancestors.tree_cycle = FALSE)) SELECT id FROM base_and_ancestors AS epics WHERE epics.id NOT IN (SELECT epics.id FROM epics WHERE epics.id = 277866) ORDER BY depth ASC) ORDER BY epics.id DESCPlan with execution
Sort (cost=1866.46..1866.46 rows=1 width=1198) (actual time=5.415..5.423 rows=0 loops=1)
Sort Key: epics.id DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=3 read=3
I/O Timings: read=5.321 write=0.000
-> Hash Semi Join (cost=1777.21..1866.45 rows=1 width=1198) (actual time=5.390..5.397 rows=0 loops=1)
Hash Cond: (epics.group_id = namespaces.id)
Buffers: shared read=3
I/O Timings: read=5.321 write=0.000
-> Nested Loop (cost=185.08..274.24 rows=26 width=1198) (actual time=5.389..5.393 rows=0 loops=1)
Buffers: shared read=3
I/O Timings: read=5.321 write=0.000
-> HashAggregate (cost=184.67..184.93 rows=26 width=4) (actual time=5.388..5.392 rows=0 loops=1)
Group Key: epics_1.id
Buffers: shared read=3
I/O Timings: read=5.321 write=0.000
-> Sort (cost=184.28..184.34 rows=26 width=8) (actual time=5.384..5.388 rows=0 loops=1)
Sort Key: epics_1.depth
Sort Method: quicksort Memory: 25kB
Buffers: shared read=3
I/O Timings: read=5.321 write=0.000
CTE base_and_ancestors
-> Recursive Union (cost=0.42..179.08 rows=51 width=1235) (actual time=5.373..5.375 rows=0 loops=1)
Buffers: shared read=3
I/O Timings: read=5.321 write=0.000
-> Index Scan using epics_pkey on public.epics epics_2 (cost=0.42..3.44 rows=1 width=1235) (actual time=5.361..5.362 rows=0 loops=1)
Index Cond: (epics_2.id = 277866)
Buffers: shared read=3
I/O Timings: read=5.321 write=0.000
-> Nested Loop (cost=0.42..17.46 rows=5 width=1235) (actual time=0.010..0.011 rows=0 loops=1)
I/O Timings: read=0.000 write=0.000
-> WorkTable Scan on base_and_ancestors (cost=0.00..0.20 rows=5 width=40) (actual time=0.009..0.009 rows=0 loops=1)
Filter: (NOT base_and_ancestors.tree_cycle)
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
-> Index Scan using epics_pkey on public.epics epics_3 (cost=0.42..3.44 rows=1 width=1198) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (epics_3.id = base_and_ancestors.parent_id)
I/O Timings: read=0.000 write=0.000
-> CTE Scan on base_and_ancestors epics_1 (cost=3.44..4.58 rows=26 width=8) (actual time=5.375..5.376 rows=0 loops=1)
Filter: (NOT (hashed SubPlan 3))
Rows Removed by Filter: 0
Buffers: shared read=3
I/O Timings: read=5.321 write=0.000
SubPlan 3
-> Index Only Scan using epics_pkey on public.epics epics_4 (cost=0.42..3.44 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (epics_4.id = 277866)
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000
-> Index Scan using epics_pkey on public.epics (cost=0.42..3.44 rows=1 width=1198) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (epics.id = epics_1.id)
I/O Timings: read=0.000 write=0.000
-> Hash (cost=1589.87..1589.87 rows=181 width=4) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> CTE Scan on base_and_descendants namespaces (cost=1584.44..1588.06 rows=181 width=4) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
CTE base_and_descendants
-> Recursive Union (cost=0.43..1584.44 rows=181 width=350) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces namespaces_1 (cost=0.43..3.45 rows=1 width=350) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (((namespaces_1.type)::text = 'Group'::text) AND (namespaces_1.id = 2593421))
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.56..157.74 rows=18 width=350) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_2 (cost=0.56..15.73 rows=2 width=350) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (namespaces_2.parent_id = base_and_descendants.id)
Filter: ((namespaces_2.type)::text = 'Group'::text)
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.0002. Finder query with include_ancestor_groups
SQL query
SELECT epics.* FROM epics WHERE epics.group_id IN (WITH RECURSIVE base_and_ancestors AS ((SELECT namespaces.* FROM namespaces WHERE namespaces.type = 'Group' AND namespaces.id = 2593421)
UNION
(SELECT namespaces.* FROM namespaces, base_and_ancestors WHERE namespaces.type = 'Group' AND namespaces.id = base_and_ancestors.parent_id)), base_and_descendants AS ((SELECT namespaces.* FROM namespaces WHERE namespaces.type = 'Group' AND namespaces.id = 2593421)
UNION
(SELECT namespaces.* FROM namespaces, base_and_descendants WHERE namespaces.type = 'Group' AND namespaces.parent_id = base_and_descendants.id)) SELECT namespaces.id FROM ((SELECT namespaces.* FROM base_and_ancestors AS namespaces WHERE namespaces.type = 'Group')
UNION
(SELECT namespaces.* FROM base_and_descendants AS namespaces WHERE namespaces.type = 'Group')) namespaces WHERE namespaces.type = 'Group') AND epics.id IN (WITH RECURSIVE base_and_ancestors AS ((SELECT 1 as depth, ARRAY[epics.id] AS tree_path, false AS tree_cycle, epics.* FROM epics WHERE epics.id = 277866)
UNION
(SELECT (base_and_ancestors.depth + 1), tree_path || epics.id, epics.id = ANY(tree_path), epics.* FROM epics, base_and_ancestors WHERE epics.id = base_and_ancestors.parent_id AND base_and_ancestors.tree_cycle = FALSE)) SELECT id FROM base_and_ancestors AS epics WHERE epics.id NOT IN (SELECT epics.id FROM epics WHERE epics.id = 277866) ORDER BY depth ASC) ORDER BY epics.id DESCPlan with execution
Sort (cost=2162.50..2162.51 rows=1 width=1198) (actual time=0.187..0.194 rows=0 loops=1)
Sort Key: epics.id DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=6
I/O Timings: read=0.000 write=0.000
-> Hash Semi Join (cost=2125.53..2162.49 rows=1 width=1198) (actual time=0.076..0.083 rows=0 loops=1)
Hash Cond: (epics.id = "ANY_subquery".id)
Buffers: shared hit=3
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=1940.61..1977.51 rows=21 width=1198) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> Unique (cost=1940.19..1940.20 rows=2 width=4) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> Sort (cost=1940.19..1940.20 rows=2 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Sort Key: namespaces.id
I/O Timings: read=0.000 write=0.000
-> Subquery Scan on namespaces (cost=1939.89..1940.16 rows=2 width=4) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
CTE base_and_ancestors
-> Recursive Union (cost=0.43..350.87 rows=21 width=350) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces namespaces_3 (cost=0.43..3.45 rows=1 width=350) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (((namespaces_3.type)::text = 'Group'::text) AND (namespaces_3.id = 2593421))
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.43..34.70 rows=2 width=350) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> WorkTable Scan on base_and_ancestors (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces namespaces_4 (cost=0.43..3.45 rows=1 width=350) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (((namespaces_4.type)::text = 'Group'::text) AND (namespaces_4.id = base_and_ancestors.parent_id))
I/O Timings: read=0.000 write=0.000
CTE base_and_descendants
-> Recursive Union (cost=0.43..1584.44 rows=181 width=350) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_namespaces_on_type_and_id_partial on public.namespaces namespaces_5 (cost=0.43..3.45 rows=1 width=350) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (((namespaces_5.type)::text = 'Group'::text) AND (namespaces_5.id = 2593421))
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.56..157.74 rows=18 width=350) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_6 (cost=0.56..15.73 rows=2 width=350) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (namespaces_6.parent_id = base_and_descendants.id)
Filter: ((namespaces_6.type)::text = 'Group'::text)
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
-> Unique (cost=4.58..4.83 rows=2 width=2946) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> Sort (cost=4.58..4.59 rows=2 width=2946) (actual time=0.000..0.000 rows=0 loops=0)
Sort Key: namespaces_1.id, namespaces_1.name, namespaces_1.path, namespaces_1.owner_id, namespaces_1.created_at, namespaces_1.updated_at, namespaces_1.type, namespaces_1.description, namespaces_1.avatar, namespaces_1.membership_lock, namespaces_1.share_with_group_lock, namespaces_1.visibility_level, namespaces_1.request_access_enabled, namespaces_1.ldap_sync_status, namespaces_1.ldap_sync_error, namespaces_1.ldap_sync_last_update_at, namespaces_1.ldap_sync_last_successful_update_at, namespaces_1.ldap_sync_last_sync_at, namespaces_1.lfs_enabled, namespaces_1.description_html, namespaces_1.parent_id, namespaces_1.shared_runners_minutes_limit, namespaces_1.repository_size_limit, namespaces_1.require_two_factor_authentication, namespaces_1.two_factor_grace_period, namespaces_1.cached_markdown_version, namespaces_1.project_creation_level, namespaces_1.runners_token, namespaces_1.file_template_project_id, namespaces_1.saml_discovery_token, namespaces_1.runners_token_encrypted, namespaces_1.custom_project_templates_group_id, namespaces_1.auto_devops_enabled, namespaces_1.extra_shared_runners_minutes_limit, namespaces_1.last_ci_minutes_notification_at, namespaces_1.last_ci_minutes_usage_notification_level, namespaces_1.subgroup_creation_level, namespaces_1.emails_disabled, namespaces_1.max_pages_size, namespaces_1.max_artifacts_size, namespaces_1.mentions_disabled, namespaces_1.default_branch_protection, namespaces_1.unlock_membership_to_ldap, namespaces_1.max_personal_access_token_lifetime, namespaces_1.push_rule_id, namespaces_1.shared_runners_enabled, namespaces_1.allow_descendants_override_disabled_shared_runners, namespaces_1.traversal_ids, namespaces_1.delayed_project_removal
I/O Timings: read=0.000 write=0.000
-> Append (cost=0.00..4.57 rows=2 width=2946) (actual time=0.000..0.000 rows=0 loops=0)
I/O Timings: read=0.000 write=0.000
-> CTE Scan on base_and_ancestors namespaces_1 (cost=0.00..0.47 rows=1 width=2946) (actual time=0.000..0.000 rows=0 loops=0)
Filter: ((namespaces_1.type)::text = 'Group'::text)
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
-> CTE Scan on base_and_descendants namespaces_2 (cost=0.00..4.07 rows=1 width=2946) (actual time=0.000..0.000 rows=0 loops=0)
Filter: ((namespaces_2.type)::text = 'Group'::text)
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_epics_on_group_id_and_iid on public.epics (cost=0.42..18.54 rows=11 width=1198) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (epics.group_id = namespaces.id)
I/O Timings: read=0.000 write=0.000
-> Hash (cost=184.60..184.60 rows=26 width=4) (actual time=0.053..0.055 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
Buffers: shared hit=3
I/O Timings: read=0.000 write=0.000
-> Subquery Scan on ANY_subquery (cost=184.28..184.60 rows=26 width=4) (actual time=0.052..0.054 rows=0 loops=1)
Buffers: shared hit=3
I/O Timings: read=0.000 write=0.000
-> Sort (cost=184.28..184.34 rows=26 width=8) (actual time=0.051..0.053 rows=0 loops=1)
Sort Key: epics_1.depth
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=3
I/O Timings: read=0.000 write=0.000
CTE base_and_ancestors
-> Recursive Union (cost=0.42..179.08 rows=51 width=1235) (actual time=0.032..0.033 rows=0 loops=1)
Buffers: shared hit=3
I/O Timings: read=0.000 write=0.000
-> Index Scan using epics_pkey on public.epics epics_2 (cost=0.42..3.44 rows=1 width=1235) (actual time=0.028..0.028 rows=0 loops=1)
Index Cond: (epics_2.id = 277866)
Buffers: shared hit=3
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.42..17.46 rows=5 width=1235) (actual time=0.003..0.004 rows=0 loops=1)
I/O Timings: read=0.000 write=0.000
-> WorkTable Scan on base_and_ancestors base_and_ancestors_1 (cost=0.00..0.20 rows=5 width=40) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (NOT base_and_ancestors_1.tree_cycle)
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
-> Index Scan using epics_pkey on public.epics epics_3 (cost=0.42..3.44 rows=1 width=1198) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (epics_3.id = base_and_ancestors_1.parent_id)
I/O Timings: read=0.000 write=0.000
-> CTE Scan on base_and_ancestors epics_1 (cost=3.44..4.58 rows=26 width=8) (actual time=0.034..0.034 rows=0 loops=1)
Filter: (NOT (hashed SubPlan 4))
Rows Removed by Filter: 0
Buffers: shared hit=3
I/O Timings: read=0.000 write=0.000
SubPlan 4
-> Index Only Scan using epics_pkey on public.epics epics_4 (cost=0.42..3.44 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (epics_4.id = 277866)
Heap Fetches: 0
I/O Timings: read=0.000 write=0.000Does this MR meet the acceptance criteria?
Conformity
-
📋 Does this MR need a changelog?-
I have included a changelog entry. -
I have not included a changelog entry because _____.
-
-
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Edited by Eugenia Grieff