Skip to content

Allow Filtering by subscribed issues in the graphql api

Brett Walker requested to merge bw-add-subscribed-filter into master

What does this MR do?

For the initial functionality, we're only supporting explicit subscriptions. This means you've either specifically turned notification on or off for an issue. This does not include implicit subscriptions, which would be issues you've commented on, awarded, etc.

Related to #14972

Original description from !111531 (closed)

NOTE: Original description below. This is in place of Allow Filtering by subscribed issues in the gra... (!111531 - closed), where the commit chain got a little boinked up.

This MR allows a user to filter issues to only the subscribed issues within the graphql api. Subscribed Issues are issues where you are receiving email notifications and where the toggle on the bottom right of the right sidebar is on.

This offers an API-based solution to the problem in this issue: #14972

However, It only exposed exlicitly subscribed issues.

Rather than doing this to close any particular issue, I'm opening this because I would personally like to use this endpoint in my day to day work. The related issue has 300 👍 emojis.

Query plan using my user id and the gitlab-org/gitlab project with 3 years of subscriptions:

SELECT "issues".* FROM "issues" INNER JOIN "subscriptions" ON "subscriptions"."subscribable_type" = 'Issue' AND "subscriptions"."subscribable_id" = "issues"."id" WHERE "issues"."project_id" = 278964 AND "subscriptions"."user_id" = 4626651 ORDER BY "issues"."created_at" DESC, "issues"."id" DESC LIMIT 101;

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/15326/commands/53339

Queries

Issues (subscribed: EXPLICITLY_UNSUBSCRIBED)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31922/commands/98710

SELECT "issues"."id",
       "issues"."title",
       "issues"."author_id",
       "issues"."project_id",
       "issues"."created_at",
       "issues"."updated_at",
       "issues"."description",
       "issues"."milestone_id",
       "issues"."iid",
       "issues"."updated_by_id",
       "issues"."weight",
       "issues"."confidential",
       "issues"."due_date",
       "issues"."moved_to_id",
       "issues"."lock_version",
       "issues"."title_html",
       "issues"."description_html",
       "issues"."time_estimate",
       "issues"."relative_position",
       "issues"."service_desk_reply_to",
       "issues"."cached_markdown_version",
       "issues"."last_edited_at",
       "issues"."last_edited_by_id",
       "issues"."discussion_locked",
       "issues"."closed_at",
       "issues"."closed_by_id",
       "issues"."state_id",
       "issues"."duplicated_to_id",
       "issues"."promoted_to_epic_id",
       "issues"."health_status",
       "issues"."external_key",
       "issues"."sprint_id",
       "issues"."blocking_issues_count",
       "issues"."upvotes_count",
       "issues"."work_item_type_id",
       "issues"."namespace_id",
       "issues"."start_date",
       "issues"."imported_from"
FROM "issues"
INNER JOIN "subscriptions" ON "subscriptions"."subscribable_type" = 'Issue'
AND "subscriptions"."subscribable_id" = "issues"."id"
WHERE "issues"."project_id" = 278964
  AND "subscriptions"."user_id" = 12417044
  AND "subscriptions"."subscribed" = TRUE
ORDER BY "issues"."created_at" DESC,
         "issues"."id" DESC LIMIT 101`

Issues (subscribed: EXPLICITLY_SUBSCRIBED)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31922/commands/98711

SELECT "issues"."id",
       "issues"."title",
       "issues"."author_id",
       "issues"."project_id",
       "issues"."created_at",
       "issues"."updated_at",
       "issues"."description",
       "issues"."milestone_id",
       "issues"."iid",
       "issues"."updated_by_id",
       "issues"."weight",
       "issues"."confidential",
       "issues"."due_date",
       "issues"."moved_to_id",
       "issues"."lock_version",
       "issues"."title_html",
       "issues"."description_html",
       "issues"."time_estimate",
       "issues"."relative_position",
       "issues"."service_desk_reply_to",
       "issues"."cached_markdown_version",
       "issues"."last_edited_at",
       "issues"."last_edited_by_id",
       "issues"."discussion_locked",
       "issues"."closed_at",
       "issues"."closed_by_id",
       "issues"."state_id",
       "issues"."duplicated_to_id",
       "issues"."promoted_to_epic_id",
       "issues"."health_status",
       "issues"."external_key",
       "issues"."sprint_id",
       "issues"."blocking_issues_count",
       "issues"."upvotes_count",
       "issues"."work_item_type_id",
       "issues"."namespace_id",
       "issues"."start_date",
       "issues"."imported_from"
FROM "issues"
INNER JOIN "subscriptions" ON "subscriptions"."subscribable_type" = 'Issue'
AND "subscriptions"."subscribable_id" = "issues"."id"
WHERE "issues"."project_id" = 278964
  AND "subscriptions"."user_id" = 12417044
  AND "subscriptions"."subscribed" = FALSE
ORDER BY "issues"."created_at" DESC,
         "issues"."id" DESC LIMIT 101

MRs (subscribed: EXPLICITLY_UNSUBSCRIBED)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31922/commands/98714

SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "subscriptions" ON "subscriptions"."subscribable_type" = 'MergeRequest'
AND "subscriptions"."subscribable_id" = "merge_requests"."id"
WHERE "merge_requests"."target_project_id" = 278964
  AND "subscriptions"."user_id" = 12417044
  AND "subscriptions"."subscribed" = FALSE
ORDER BY "merge_requests"."created_at" DESC,
         "merge_requests"."id" DESC LIMIT 101`

MRs (subscribed: EXPLICITLY_SUBSCRIBED)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31922/commands/98715

SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN "subscriptions" ON "subscriptions"."subscribable_type" = 'MergeRequest'
AND "subscriptions"."subscribable_id" = "merge_requests"."id"
WHERE "merge_requests"."target_project_id" = 278964
  AND "subscriptions"."user_id" = 12417044
  AND "subscriptions"."subscribed" = TRUE
ORDER BY "merge_requests"."created_at" DESC,
         "merge_requests"."id" DESC LIMIT 101

WorkItems (subscribed: EXPLICITLY_UNSUBSCRIBED)

This is the same query as for issues

WorkItems (subscribed: EXPLICITLY_SUBSCRIBED)

This is the same query as for issues

Epics (subscribed: EXPLICITLY_UNSUBSCRIBED)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31922/commands/98717

SELECT "epics".*
FROM "epics"
INNER JOIN "subscriptions" ON "subscriptions"."subscribable_type" = 'Epic'
AND "subscriptions"."subscribable_id" = "epics"."id"
WHERE "epics"."group_id" IN
    (SELECT "namespaces"."id"
     FROM "namespaces"
     WHERE "namespaces"."type" = 'Group'
       AND (traversal_ids @> ('{9970}')))
  AND "subscriptions"."user_id" = 12417044
  AND "subscriptions"."subscribed" = FALSE
ORDER BY "epics"."id" DESC LIMIT 101

Epics (subscribed: EXPLICITLY_SUBSCRIBED)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31922/commands/98718

SELECT "epics".*
FROM "epics"
INNER JOIN "subscriptions" ON "subscriptions"."subscribable_type" = 'Epic'
AND "subscriptions"."subscribable_id" = "epics"."id"
WHERE "epics"."group_id" IN
    (SELECT "namespaces"."id"
     FROM "namespaces"
     WHERE "namespaces"."type" = 'Group'
       AND (traversal_ids @> ('{9970}')))
  AND "subscriptions"."user_id" = 12417044
  AND "subscriptions"."subscribed" = TRUE
ORDER BY "epics"."id" DESC LIMIT 101

Screenshots or Screencasts (strongly suggested)

image

How to set up and validate locally

  1. Go the project flightjs/Flight on your local system.

  2. Go into an issue and turn off and then on the notifications for the issue (either in the triple dot menu, or the new notification icon in the upper right of an issue). This ensures that the issue is explicitly subscribed.

  3. Go into another issue and turn off the notifications.

  4. Go to the graphql explorer, such as http://ee.gitlab.test:5100/-/graphql-explorer, and use the following query

     query {
   project(fullPath: "flightjs/Flight") {
     issues(subscribed: EXPLICITLY_SUBSCRIBED) {
       nodes {
         id
         subscribed
       }
     }
   }
 }

  5. you should see the listed just the issue with the notifications explicitly turned on.

  6. Use EXPLICITLY_UNSUBSCRIBED in the query and you should see the issue with the notifications turned off.

  7. Note that if you remove the subscribed parameter, and show all issues, the subscribed attribute that is show will reflect explicit and implicit subscriptions. This is ok for this iteration.

MR acceptance checklist

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

Edited by Brett Walker

Merge request reports