Skip to content

Expose timelogs in GraphQL query type and add user/project filter

Lee Tickett requested to merge 3918-graphql-expose-timelogs-at-root into master

What does this MR do?

We recently introduced the first Time Tracking report for issuables !60161 (merged)

We need to deliver on further reporting requirements by allowing Time Tracking reports at project and user levels.

This MR improves the GraphQL querying capabilities by exposing timelogs against the query type and allowing any combination of filtering on:

  • date range
  • group
  • project
  • user

Screenshots or Screencasts (strongly suggested)

How to setup and validate locally (strongly suggested)

GraphQL Queries:

Should get all 6 back...
query {
  timelogs { 
      nodes {
        user { username }
        spentAt
        timeSpent
    }
  }
}

Should get 2 back from root (30m/120m)...
query {
  timelogs(groupId: "gid://gitlab/Group/22") { 
      nodes {
        user { username }
        spentAt
        timeSpent
    }
  }
}

Should get 4 back, 2 from root, 2 from rosenda_watsica...
query {
  timelogs(projectId: "gid://gitlab/Project/6") { 
      nodes { 
        user { username }
        spentAt
        timeSpent
    }
  }
}

Should get 2 back from rosenda_wasica...
query {
  timelogs(username: "rosenda_watsica") { 
      nodes { 
        user { username }
        spentAt
        timeSpent
    }
  }
}

Should get 5 back (all except the 120m)...
query {
  timelogs(startDate: "2021-01-01", endDate: "2021-02-28") { 
      nodes { 
        user { username }
        spentAt
        timeSpent
    }
  }
}
Database
query {
  timelogs { 
      nodes {
        user { username }
        spentAt
        timeSpent
    }
  }
}

SELECT "timelogs".* FROM "timelogs" ORDER BY "timelogs"."id" DESC LIMIT 100

 Limit  (cost=0.43..5.79 rows=100 width=84) (actual time=0.031..0.240 rows=100 loops=1)
   Buffers: shared hit=101
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using timelogs_pkey on public.timelogs  (cost=0.43..173800.35 rows=3245325 width=84) (actual time=0.029..0.223 rows=100 loops=1)
         Buffers: shared hit=101
         I/O Timings: read=0.000 write=0.000

Time: 0.417 ms
  - planning: 0.144 ms
  - execution: 0.273 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 101 (~808.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

query {
  timelogs(groupId: "gid://gitlab/Group/22") { 
      nodes {
        user { username }
        spentAt
        timeSpent
    }
  }
}

SELECT "timelogs".* FROM "timelogs" INNER JOIN "projects" ON "projects"."id" = "timelogs"."project_id" WHERE "projects"."namespace_id" IN (WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 22)
UNION
(SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT id FROM "base_and_descendants" AS "namespaces") ORDER BY "timelogs"."id" DESC LIMIT 100

 Limit  (cost=13935.07..13935.32 rows=100 width=84) (actual time=2.743..2.745 rows=0 loops=1)
   Buffers: shared hit=3 read=3
   I/O Timings: read=2.598 write=0.000
   ->  Sort  (cost=13935.07..13936.71 rows=656 width=84) (actual time=2.741..2.744 rows=0 loops=1)
         Sort Key: timelogs.id DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=3 read=3
         I/O Timings: read=2.598 write=0.000
         ->  Nested Loop  (cost=1592.55..13910.00 rows=656 width=84) (actual time=2.694..2.697 rows=0 loops=1)
               Buffers: shared read=3
               I/O Timings: read=2.598 write=0.000
               ->  Nested Loop  (cost=1592.12..2129.71 rows=4087 width=4) (actual time=2.693..2.696 rows=0 loops=1)
                     Buffers: shared read=3
                     I/O Timings: read=2.598 write=0.000
                     ->  HashAggregate  (cost=1591.69..1593.69 rows=200 width=4) (actual time=2.693..2.695 rows=0 loops=1)
                           Group Key: namespaces.id
                           Buffers: shared read=3
                           I/O Timings: read=2.598 write=0.000
                           ->  CTE Scan on base_and_descendants namespaces  (cost=1585.15..1589.17 rows=201 width=4) (actual time=2.658..2.660 rows=0 loops=1)
                                 Buffers: shared read=3
                                 I/O Timings: read=2.598 write=0.000
                                 CTE base_and_descendants
                                   ->  Recursive Union  (cost=0.43..1585.15 rows=201 width=356) (actual time=2.657..2.658 rows=0 loops=1)
                                         Buffers: shared read=3
                                         I/O Timings: read=2.598 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=356) (actual time=2.652..2.653 rows=0 loops=1)
                                               Index Cond: (((namespaces_1.type)::text = 'Group'::text) AND (namespaces_1.id = 22))
                                               Buffers: shared read=3
                                               I/O Timings: read=2.598 write=0.000
                                         ->  Nested Loop  (cost=0.56..157.77 rows=20 width=356) (actual time=0.003..0.004 rows=0 loops=1)
                                               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.003..0.003 rows=0 loops=1)
                                                     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.74 rows=2 width=356) (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.000
                     ->  Index Only Scan using index_projects_on_namespace_id_and_id on public.projects  (cost=0.44..2.48 rows=20 width=8) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: (projects.namespace_id = namespaces.id)
                           Heap Fetches: 0
                           I/O Timings: read=0.000 write=0.000
               ->  Index Scan using index_timelogs_on_project_id_and_spent_at on public.timelogs  (cost=0.43..1.99 rows=89 width=84) (actual time=0.000..0.000 rows=0 loops=0)
                     Index Cond: (timelogs.project_id = projects.id)
                     I/O Timings: read=0.000 write=0.000

Time: 8.966 ms
  - planning: 5.925 ms
  - execution: 3.041 ms
    - I/O read: 2.598 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 3 (~24.00 KiB) from the buffer pool
  - reads: 3 (~24.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

query {
  timelogs(projectId: "gid://gitlab/Project/6") { 
      nodes { 
        user { username }
        spentAt
        timeSpent
    }
  }
}

SELECT "timelogs".* FROM "timelogs" WHERE "timelogs"."project_id" = 6 ORDER BY "timelogs"."id" DESC LIMIT 100

 Limit  (cost=66.37..66.48 rows=45 width=84) (actual time=4.496..4.498 rows=0 loops=1)
   Buffers: shared hit=3 read=3
   I/O Timings: read=4.397 write=0.000
   ->  Sort  (cost=66.37..66.48 rows=45 width=84) (actual time=4.494..4.495 rows=0 loops=1)
         Sort Key: timelogs.id DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=3 read=3
         I/O Timings: read=4.397 write=0.000
         ->  Index Scan using index_timelogs_on_project_id_and_spent_at on public.timelogs  (cost=0.43..65.13 rows=45 width=84) (actual time=4.475..4.475 rows=0 loops=1)
               Index Cond: (timelogs.project_id = 6)
               Buffers: shared read=3
               I/O Timings: read=4.397 write=0.000

Time: 4.743 ms
  - planning: 0.213 ms
  - execution: 4.530 ms
    - I/O read: 4.397 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 3 (~24.00 KiB) from the buffer pool
  - reads: 3 (~24.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

query {
  timelogs(username: "rosenda_watsica") { 
      nodes { 
        user { username }
        spentAt
        timeSpent
    }
  }
}

SELECT "timelogs".* FROM "timelogs" WHERE "timelogs"."user_id" = 6 ORDER BY "timelogs"."id" DESC LIMIT 100

 Limit  (cost=80.01..80.15 rows=56 width=84) (actual time=2.913..2.914 rows=0 loops=1)
   Buffers: shared hit=3 read=3
   I/O Timings: read=2.743 write=0.000
   ->  Sort  (cost=80.01..80.15 rows=56 width=84) (actual time=2.910..2.911 rows=0 loops=1)
         Sort Key: timelogs.id DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=3 read=3
         I/O Timings: read=2.743 write=0.000
         ->  Index Scan using index_timelogs_on_user_id on public.timelogs  (cost=0.43..78.39 rows=56 width=84) (actual time=2.822..2.822 rows=0 loops=1)
               Index Cond: (timelogs.user_id = 6)
               Buffers: shared read=3
               I/O Timings: read=2.743 write=0.000

Time: 3.140 ms
  - planning: 0.193 ms
  - execution: 2.947 ms
    - I/O read: 2.743 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 3 (~24.00 KiB) from the buffer pool
  - reads: 3 (~24.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

query {
  timelogs(startDate: "2021-01-01", endDate: "2021-02-28") { 
      nodes { 
        user { username }
        spentAt
        timeSpent
    }
  }
}

 SELECT "timelogs".* FROM "timelogs" WHERE (spent_at >= '2021-01-01 00:00:00') AND (spent_at <= '2021-02-28 23:59:59.999999') ORDER BY "timelogs"."id" DESC LIMIT 100

 Limit  (cost=0.43..135.42 rows=100 width=84) (actual time=9.099..40.421 rows=100 loops=1)
   Buffers: shared hit=61648
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using timelogs_pkey on public.timelogs  (cost=0.43..190026.98 rows=140767 width=84) (actual time=9.097..40.400 rows=100 loops=1)
         Filter: ((timelogs.spent_at >= '2021-01-01 00:00:00+00'::timestamp with time zone) AND (timelogs.spent_at <= '2021-02-28 23:59:59.999999+00'::timestamp with time zone))
         Rows Removed by Filter: 63277
         Buffers: shared hit=61648
         I/O Timings: read=0.000 write=0.000

Time: 40.712 ms
  - planning: 0.231 ms
  - execution: 40.481 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 61648 (~481.60 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

query {
  timelogs(projectId: "gid://gitlab/Project/6", username: "rosenda_watsica", startDate: "2021-01-01", endDate: "2021-02-28") { 
      nodes { 
        user { username }
        spentAt
        timeSpent
    }
  }
}

SELECT "timelogs".* FROM "timelogs" WHERE "timelogs"."project_id" = 6 AND "timelogs"."user_id" = 6 AND (spent_at >= '2021-01-01 00:00:00') AND (spent_at <= '2021-02-28 23:59:59.999999') ORDER BY "timelogs"."id" DESC LIMIT 100

 Limit  (cost=4.87..4.88 rows=1 width=84) (actual time=0.059..0.060 rows=0 loops=1)
   Buffers: shared hit=6
   I/O Timings: read=0.000 write=0.000
   ->  Sort  (cost=4.87..4.88 rows=1 width=84) (actual time=0.058..0.058 rows=0 loops=1)
         Sort Key: timelogs.id DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=6
         I/O Timings: read=0.000 write=0.000
         ->  Index Scan using index_timelogs_on_project_id_and_spent_at on public.timelogs  (cost=0.43..4.86 rows=1 width=84) (actual time=0.036..0.036 rows=0 loops=1)
               Index Cond: ((timelogs.project_id = 6) AND (timelogs.spent_at >= '2021-01-01 00:00:00+00'::timestamp with time zone) AND (timelogs.spent_at <= '2021-02-28 23:59:59.999999+00'::timestamp with time zone))
               Filter: (timelogs.user_id = 6)
               Rows Removed by Filter: 0
               Buffers: shared hit=3
               I/O Timings: read=0.000 write=0.000

Time: 0.466 ms
  - planning: 0.376 ms
  - execution: 0.090 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 6 (~48.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

Does this MR contain changes to processing or storing of credentials or tokens, authorization and authentication methods or other items described in the security review guidelines? If not, then delete this Security section.

  • 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 Lee Tickett

Merge request reports

Loading