Fetch iterations by cadence id
What does this MR do?
Allow filtering iterations by a given cadence: #326185 (closed)
GraphQL query:
query {
group(fullPath: "top-group/sub-group") {
iterations(iterationsCadenceId: "gid://gitlab/Iterations::Cadence/2") {
nodes {
id
title
}
}
}
}- response:
{ "data": { "group": { "iterations": { "nodes": [ { "id": "gid://gitlab/Iteration/8", "title": "Iteration 1 Jan" }, { "id": "gid://gitlab/Iteration/9", "title": "Iteration 2 Jan" }, { "id": "gid://gitlab/Iteration/10", "title": "Iteration 3 Jan" }, { "id": "gid://gitlab/Iteration/27", "title": "March 20, 2021 - March 26, 2021" }, { "id": "gid://gitlab/Iteration/28", "title": "March 27, 2021 - April 02, 2021" } ] } } } }
Screenshots (strongly suggested)
Database
We are basically adding filtering of iterations by iterations_cadence_id which has an index
NOTE: The cadences are not yet enabled on production so here is a staging query example that checks for iterations for a given cadence by looking up in ancestor groups as well:
- Lookup iterations by cadence
-
in project
details
query { project(fullPath: "gitlab-org/build/omnibus-mirror/config_guess") { iterations(includeAncestors: false, iterationsCadenceId: "gid://gitlab/Iterations::Cadence/2") { nodes { id title } } } }explain (buffers, analyze) SELECT "sprints".* FROM ((SELECT "sprints".* FROM "sprints" WHERE "sprints"."project_id" = 2645585) UNION ALL (SELECT "sprints".* FROM "sprints" WHERE 1=0)) sprints WHERE "sprints"."iterations_cadence_id" = 277 ORDER BY due_date ASC NULLS LAST, "sprints"."title" ASC LIMIT 100;QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Limit (cost=3.17..3.17 rows=1 width=217) (actual time=0.028..0.028 rows=0 loops=1) Buffers: shared hit=1 -> Sort (cost=3.17..3.17 rows=1 width=217) (actual time=0.027..0.028 rows=0 loops=1) Sort Key: sprints.due_date, sprints.title Sort Method: quicksort Memory: 25kB Buffers: shared hit=1 -> Append (cost=0.12..3.16 rows=1 width=217) (actual time=0.018..0.019 rows=0 loops=1) Buffers: shared hit=1 -> Index Scan using iteration_start_and_due_daterange_project_id_constraint on sprints (cost=0.12..3.15 rows=1 width=217) (actual time=0.017..0.017 rows=0 loops=1) Filter: ((project_id = 2645585) AND (iterations_cadence_id = 277)) Buffers: shared hit=1 Planning Time: 0.349 ms Execution Time: 0.061 ms-
including ancestors
details
query { project(fullPath: "gitlab-org/build/omnibus-mirror/config_guess") { iterations(includeAncestors: true, iterationsCadenceId: "gid://gitlab/Iterations::Cadence/2") { nodes { id title } } } }explain (buffers, analyze) SELECT "sprints".* FROM ((SELECT "sprints".* FROM "sprints" WHERE "sprints"."project_id" = 2645585) UNION ALL (SELECT "sprints".* FROM "sprints" WHERE "sprints"."group_id" IN (1920510, 1920469, 9970))) sprints WHERE "sprints"."iterations_cadence_id" = 277 ORDER BY due_date ASC NULLS LAST, "sprints"."title" ASC LIMIT 100;QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=6.33..6.34 rows=2 width=217) (actual time=1.198..1.201 rows=1 loops=1) Buffers: shared hit=60 -> Sort (cost=6.33..6.34 rows=2 width=217) (actual time=1.198..1.199 rows=1 loops=1) Sort Key: sprints.due_date, sprints.title Sort Method: quicksort Memory: 25kB Buffers: shared hit=60 -> Append (cost=0.12..6.32 rows=2 width=217) (actual time=1.165..1.167 rows=1 loops=1) Buffers: shared hit=54 -> Index Scan using iteration_start_and_due_daterange_project_id_constraint on sprints (cost=0.12..3.15 rows=1 width=217) (actual time=0.949..0.950 rows=0 loops=1) Filter: ((project_id = 2645585) AND (iterations_cadence_id = 277)) Buffers: shared hit=28 -> Index Scan using iteration_start_and_due_date_iterations_cadence_id_constraint on sprints sprints_1 (cost=0.12..3.15 rows=1 width=217) (actual time=0.214..0.215 rows=1 loops=1) Index Cond: (iterations_cadence_id = 277) Filter: (group_id = ANY ('{1920510,1920469,9970}'::bigint[])) Buffers: shared hit=26 Planning Time: 2.238 ms Execution Time: 1.297 ms-
by
openedstatedetails
query { project(fullPath: "gitlab-org/build/omnibus-mirror/config_guess") { iterations(includeAncestors: false, state: opened, iterationsCadenceId: "gid://gitlab/Iterations::Cadence/2") { nodes { id title } } } }explain (buffers, analyze) SELECT "sprints".* FROM ((SELECT "sprints".* FROM "sprints" WHERE "sprints"."project_id" = 2645585) UNION ALL (SELECT "sprints".* FROM "sprints" WHERE "sprints"."group_id" IN (1920510, 1920469, 9970))) sprints WHERE (("sprints"."state_enum" IN (2)) OR ("sprints"."state_enum" IN (1))) AND "sprints"."iterations_cadence_id" = 277 ORDER BY due_date ASC NULLS LAST, "sprints"."title" ASC LIMIT 100;QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=6.34..6.35 rows=2 width=217) (actual time=0.044..0.045 rows=0 loops=1) Buffers: shared hit=3 -> Sort (cost=6.34..6.35 rows=2 width=217) (actual time=0.043..0.043 rows=0 loops=1) Sort Key: sprints.due_date, sprints.title Sort Method: quicksort Memory: 25kB Buffers: shared hit=3 -> Append (cost=0.12..6.33 rows=2 width=217) (actual time=0.036..0.036 rows=0 loops=1) Buffers: shared hit=3 -> Index Scan using iteration_start_and_due_daterange_project_id_constraint on sprints (cost=0.12..3.15 rows=1 width=217) (actual time=0.011..0.011 rows=0 loops=1) Filter: ((project_id = 2645585) AND (iterations_cadence_id = 277) AND ((state_enum = 2) OR (state_enum = 1))) Buffers: shared hit=1 -> Index Scan using iteration_start_and_due_date_iterations_cadence_id_constraint on sprints sprints_1 (cost=0.12..3.15 rows=1 width=217) (actual time=0.024..0.024 rows=0 loops=1) Index Cond: (iterations_cadence_id = 277) Filter: ((group_id = ANY ('{1920510,1920469,9970}'::bigint[])) AND ((state_enum = 2) OR (state_enum = 1))) Rows Removed by Filter: 1 Buffers: shared hit=2 Planning Time: 0.330 ms Execution Time: 0.076 ms-
by timeframe
details
query { project(fullPath: "gitlab-org/build/omnibus-mirror/config_guess") { iterations(includeAncestors: false, state: opened, timeframe: {start: "2019-01-01", end: "2020-01-01"}, iterationsCadenceId: "gid://gitlab/Iterations::Cadence/2") { nodes { id title } } } }explain (buffers, analyze) SELECT "sprints".* FROM ((SELECT "sprints".* FROM "sprints" WHERE "sprints"."project_id" = 2645585) UNION ALL (SELECT "sprints".* FROM "sprints" WHERE "sprints"."group_id" IN (1920510, 1920469, 9970))) sprints WHERE (("sprints"."state_enum" IN (2)) OR ("sprints"."state_enum" IN (1))) AND (start_date <= '2020-01-01') AND (due_date >= '2019-01-01') AND "sprints"."iterations_cadence_id" = 277 ORDER BY due_date ASC NULLS LAST, "sprints"."title" ASC LIMIT 100;QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=6.35..6.36 rows=2 width=217) (actual time=0.096..0.097 rows=0 loops=1) Buffers: shared hit=3 -> Sort (cost=6.35..6.36 rows=2 width=217) (actual time=0.095..0.096 rows=0 loops=1) Sort Key: sprints.due_date, sprints.title Sort Method: quicksort Memory: 25kB Buffers: shared hit=3 -> Append (cost=0.12..6.34 rows=2 width=217) (actual time=0.090..0.091 rows=0 loops=1) Buffers: shared hit=3 -> Index Scan using iteration_start_and_due_daterange_project_id_constraint on sprints (cost=0.12..3.15 rows=1 width=217) (actual time=0.014..0.014 rows=0 loops=1) Filter: ((start_date <= '2020-01-01'::date) AND (due_date >= '2019-01-01'::date) AND (project_id = 2645585) AND (iterations_cadence_id = 277) AND ((state_enum = 2) OR (state_enum = 1) )) Buffers: shared hit=1 -> Index Scan using iteration_start_and_due_date_iterations_cadence_id_constraint on sprints sprints_1 (cost=0.12..3.16 rows=1 width=217) (actual time=0.075..0.075 rows=0 loops=1) Index Cond: (iterations_cadence_id = 277) Filter: ((start_date <= '2020-01-01'::date) AND (due_date >= '2019-01-01'::date) AND (group_id = ANY ('{1920510,1920469,9970}'::bigint[])) AND ((state_enum = 2) OR (state_enum = 1))) Rows Removed by Filter: 1 Buffers: shared hit=2 Planning Time: 0.954 ms Execution Time: 0.207 ms
-
-
-
-
in group
details
query { group(fullPath: "gitlab-org/build/omnibus-mirror") { iterations(includeAncestors: false, iterationsCadenceId: "gid://gitlab/Iterations::Cadence/2") { nodes { id title } } } }explain (buffers, analyze) SELECT "sprints".* FROM ((SELECT "sprints".* FROM "sprints" WHERE 1=0) UNION ALL (SELECT "sprints".* FROM "sprints" WHERE "sprints"."group_id" = 1920510)) sprints WHERE "sprints"."iterations_cadence_id" = 277 ORDER BY due_date ASC NULLS LAST, "sprints"."title" ASC LIMIT 100;QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=3.17..3.17 rows=1 width=217) (actual time=0.083..0.084 rows=1 loops=1) Buffers: shared hit=2 -> Sort (cost=3.17..3.17 rows=1 width=217) (actual time=0.082..0.083 rows=1 loops=1) Sort Key: sprints.due_date, sprints.title Sort Method: quicksort Memory: 25kB Buffers: shared hit=2 -> Append (cost=0.12..3.16 rows=1 width=217) (actual time=0.070..0.071 rows=1 loops=1) Buffers: shared hit=2 -> Index Scan using iteration_start_and_due_date_iterations_cadence_id_constraint on sprints (cost=0.12..3.15 rows=1 width=217) (actual time=0.069..0.069 rows=1 loops=1) Index Cond: (iterations_cadence_id = 277) Filter: (group_id = 1920510) Buffers: shared hit=2 Planning Time: 0.266 ms Execution Time: 0.114 ms-
including ancestors
details
query { group(fullPath: "gitlab-org/build/omnibus-mirror") { iterations(includeAncestors: true, iterationsCadenceId: "gid://gitlab/Iterations::Cadence/2") { nodes { id title } } } }explain (buffers, analyze) SELECT "sprints".* FROM ((SELECT "sprints".* FROM "sprints" WHERE 1=0) UNION ALL (SELECT "sprints".* FROM "sprints" WHERE "sprints"."group_id" IN (1920510, 1920469, 9970))) sprints WHERE "sprints"."iterations_cadence_id" = 277 ORDER BY due_date ASC NULLS LAST, "sprints"."title" ASC LIMIT 100;QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=3.17..3.18 rows=1 width=221) (actual time=0.062..0.063 rows=1 loops=1) Buffers: shared hit=2 -> Sort (cost=3.17..3.18 rows=1 width=221) (actual time=0.060..0.061 rows=1 loops=1) Sort Key: sprints.due_date, sprints.title Sort Method: quicksort Memory: 25kB Buffers: shared hit=2 -> Append (cost=0.12..3.16 rows=1 width=221) (actual time=0.051..0.052 rows=1 loops=1) Buffers: shared hit=2 -> Index Scan using iteration_start_and_due_date_iterations_cadence_id_constraint on sprints (cost=0.12..3.15 rows=1 width=221) (actual time=0.050..0.051 rows=1 loops=1) Index Cond: (iterations_cadence_id = 277) Filter: (group_id = ANY ('{1920510,1920469,9970}'::bigint[])) Buffers: shared hit=2 Planning Time: 0.310 ms Execution Time: 0.115 ms-
by timeframe
details
query { group(fullPath: "gitlab-org/build/omnibus-mirror") { iterations(includeAncestors: false, state: opened, timeframe: {start: "2019-01-01", end: "2020-01-01"}, iterationsCadenceId: "gid://gitlab/Iterations::Cadence/2") { nodes { id title } } } }explain (buffers, analyze) SELECT "sprints".* FROM ((SELECT "sprints".* FROM "sprints" WHERE 1=0) UNION ALL (SELECT "sprints".* FROM "sprints" WHERE "sprints"."group_id" IN (1920510, 1920469, 9970))) sprints WHERE (("sprints"."state_enum" IN (2)) OR ("sprints"."state_enum" IN (1))) AND (start_date <= '2020-01-01') AND (due_date >= '2019-01-01') AND "sprints"."iterations_cadence_id" = 277 ORDER BY due_date ASC NULLS LAST, "sprints"."title" ASC LIMIT 100;QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=3.18..3.19 rows=1 width=217) (actual time=0.062..0.063 rows=0 loops=1) Buffers: shared hit=2 -> Sort (cost=3.18..3.19 rows=1 width=217) (actual time=0.061..0.062 rows=0 loops=1) Sort Key: sprints.due_date, sprints.title Sort Method: quicksort Memory: 25kB Buffers: shared hit=2 -> Append (cost=0.12..3.17 rows=1 width=217) (actual time=0.040..0.040 rows=0 loops=1) Buffers: shared hit=2 -> Index Scan using iteration_start_and_due_date_iterations_cadence_id_constraint on sprints (cost=0.12..3.16 rows=1 width=217) (actual time=0.039..0.039 rows=0 loops=1) Index Cond: (iterations_cadence_id = 277) Filter: ((start_date <= '2020-01-01'::date) AND (due_date >= '2019-01-01'::date) AND (group_id = ANY ('{1920510,1920469,9970}'::bigint[])) AND ((state_enum = 2) OR (state_enum = 1))) Rows Removed by Filter: 1 Buffers: shared hit=2 Planning Time: 0.425 ms Execution Time: 0.091 ms
-
-
-
Does 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
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. -
Tested in all supported browsers -
Informed Infrastructure department of a default or new setting change, if applicable per definition of done
Security
If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:
-
Label as security and @ mention @gitlab-com/gl-security/appsec -
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods -
Security reports checked/validated by a reviewer from the AppSec team