Skip to content

Filter issue lists and issue boards by current iteration

Alexandru Croitor requested to merge add-be-for-scoping-issue-boards-to-current-iteration into master

What does this MR do?

  • Introduces the ability to filter an issue list and an issue board by current iteration on BE
    • by saving the iteration_id in boards table
    • this is going to actually be implemented in !49012 (merged)
  • Introduces the ability to filter issues by current iteration.
    • in issues list
    • in issue board

Database

  • Adds iteration_id column to boards table
  • Adds index on iterations_id column
  • No FK to sprints table as we will be storing values that break the FK constraint, similar to boards#milestone_id

Up

== 20201117203224 AddIterationIdToBoardsTable: migrating ======================
-- add_column(:boards, :iteration_id, :bigint)
   -> 0.0008s
== 20201117203224 AddIterationIdToBoardsTable: migrated (0.0041s) =============

== 20201117213024 AddIterationIdIndexToBoardsTable: migrating =================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:boards, :iteration_id, {:name=>"index_boards_on_iteration_id", :algorithm=>:concurrently})
   -> 0.0017s
-- add_index(:boards, :iteration_id, {:name=>"index_boards_on_iteration_id", :algorithm=>:concurrently})
   -> 0.0023s
== 20201117213024 AddIterationIdIndexToBoardsTable: migrated (0.0046s) ========

Down

== 20201117213024 AddIterationIdIndexToBoardsTable: reverting =================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:boards, :iteration_id, {:name=>"index_boards_on_iteration_id", :algorithm=>:concurrently})
   -> 0.0026s
-- remove_index(:boards, {:name=>"index_boards_on_iteration_id", :algorithm=>:concurrently, :column=>:iteration_id})
   -> 0.0038s
== 20201117213024 AddIterationIdIndexToBoardsTable: reverted (0.0068s) ========

== 20201117203224 AddIterationIdToBoardsTable: reverting ======================
-- remove_column(:boards, :iteration_id)
   -> 0.0004s
== 20201117203224 AddIterationIdToBoardsTable: reverted (0.0018s) =============

Queries of interest:

Find current iteration

Following query is generated when looking-up current iteration for gitlab-org/gitlab:

SELECT "sprints".* FROM ((SELECT "sprints".* FROM "sprints" WHERE 1=0)
UNION ALL
(SELECT "sprints".* FROM "sprints" WHERE "sprints"."group_id" IN (9970))) sprints WHERE (("sprints"."state_enum" IN (2)) OR ("sprints"."state_enum" IN (1))) AND (start_date is not NULL or due_date is not NULL) AND (start_date is NULL or start_date <= '2020-11-30') AND (due_date is NULL or due_date >= '2020-11-30') ORDER BY due_date ASC NULLS LAST, "sprints"."title" ASC LIMIT 1
Limit  (cost=22.27..22.27 rows=1 width=258) (actual time=11.556..11.559 rows=1 loops=1)
   Buffers: shared hit=9 read=15 dirtied=1
   I/O Timings: read=8.928
   ->  Sort  (cost=22.27..22.28 rows=2 width=258) (actual time=11.555..11.556 rows=1 loops=1)
         Sort Key: sprints.due_date, sprints.title
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=9 read=15 dirtied=1
         I/O Timings: read=8.928
         ->  Append  (cost=0.28..22.26 rows=2 width=258) (actual time=2.579..11.483 rows=1 loops=1)
               Buffers: shared hit=3 read=15 dirtied=1
               I/O Timings: read=8.928
               ->  Index Scan using index_sprints_on_group_id on public.sprints  (cost=0.28..22.23 rows=2 width=258) (actual time=2.578..11.479 rows=1 loops=1)
                     Index Cond: (sprints.group_id = 9970)
                     Filter: (((sprints.start_date IS NOT NULL) OR (sprints.due_date IS NOT NULL)) AND ((sprints.start_date IS NULL) OR (sprints.start_date <= '2020-11-30'::date)) AND ((sprints.due_date IS NULL) OR (sprints.due_date >= '2020-11-30'::date)) AND ((sprints.state_enum = 2) OR (sprints.state_enum = 1)))
                     Rows Removed by Filter: 13
                     Buffers: shared hit=3 read=15 dirtied=1
                     I/O Timings: read=8.928

Time: 12.227 ms
  - planning: 0.590 ms
  - execution: 11.637 ms
    - I/O read: 8.928 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 9 (~72.00 KiB) from the buffer pool
  - reads: 15 (~120.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 1 (~8.00 KiB)
  - writes: 0

re #196804 (closed)

Screenshots (strongly suggested)

Filter by current iteration on issue lists

issue-list-filter-by-current-iteration

Filter by current iteration on issue boards

issue-bpard-filter-by-current-iteration

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

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
Edited by Alexandru Croitor

Merge request reports