Query backend for tasks by type chart
What does this MR do?
This MR implements the count queries required to implement the tasks by type chart. We use the MergeRequestsFinder and IssuesFinder classes as base query.
Feature flag needs to be enabled (rails c):
Feature.enable(Gitlab::Analytics::TASKS_BY_TYPE_CHART_FEATURE_FLAG)Example url:
-/analytics/type_of_work/tasks_by_type?group_id=$GROUP_FULL_PATH&label_ids[]=$LABEL_ID_1&label_ids[]=$LABEL_ID_2&created_after=2018-01-01&subject=Issue&created_before=2100-01-01
-/analytics/type_of_work/tasks_by_type?group_id=$GROUP_FULL_PATH&label_ids[]=$LABEL_ID_1&label_ids[]=$LABEL_ID_2&created_after=2018-01-01&subject=MergeRequest&created_before=2100-01-01
Queries:
gitlab-org, real label ids and user
MergeRequest:
Small note: in another MR I'm planning to add a new index (project_id, created_at), that'll probably improve the execution time of this query.
SELECT COUNT("merge_requests"."id") AS count_struct_arel_attributes_attribute_relation_arel_table_0x00,
"labels"."id" AS labels_id,
DATE("merge_requests"."created_at") AS date_merge_requests_created_at
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
INNER JOIN "label_links" ON "label_links"."target_id" = "merge_requests"."id"
AND "label_links"."target_type" = 'MergeRequest'
INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE "projects"."namespace_id" IN
(WITH RECURSIVE "base_and_descendants" AS
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" IN ('Group')
AND "namespaces"."id" = 9970
UNION SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" IN ('Group')
AND "namespaces"."parent_id" = "base_and_descendants"."id") SELECT "namespaces"."id"
FROM "base_and_descendants" AS "namespaces")
AND (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 20))
OR projects.visibility_level IN (10,
20))
AND ("project_features"."merge_requests_access_level" IS NULL
OR "project_features"."merge_requests_access_level" IN (20,
30)
OR ("project_features"."merge_requests_access_level" = 10
AND EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 20))))
AND "merge_requests"."created_at" <= '2019-08-01'
AND "merge_requests"."created_at" >= '2019-05-01'
AND "labels"."id" IN (2731248,
10230929,
4116705,
2492649,
2278648,
2779806)
GROUP BY "labels"."id",
DATE("merge_requests"."created_at")
Issue:
SELECT COUNT("issues"."id") AS count_struct_arel_attributes_attribute_relation_arel_table_0x00,
"labels"."id" AS labels_id,
DATE("issues"."created_at") AS date_issues_created_at
FROM "issues"
INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
INNER JOIN "label_links" ON "label_links"."target_id" = "issues"."id"
AND "label_links"."target_type" = 'Issue'
INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (issues.confidential IS NOT TRUE
OR (issues.confidential = TRUE
AND (issues.author_id = 4156052
OR EXISTS
(SELECT TRUE
FROM issue_assignees
WHERE user_id = 4156052
AND issue_id = issues.id)
OR EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = issues.project_id)
AND (project_authorizations.access_level >= 20)))))
AND "projects"."namespace_id" IN
(WITH RECURSIVE "base_and_descendants" AS
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" IN ('Group')
AND "namespaces"."id" = 9970
UNION SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" IN ('Group')
AND "namespaces"."parent_id" ="base_and_descendants"."id") SELECT "namespaces"."id"
FROM "base_and_descendants" AS "namespaces")
AND (EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 10))
OR projects.visibility_level IN (10,
20))
AND ("project_features"."issues_access_level" IS NULL
OR "project_features"."issues_access_level" IN (20,
30)
OR ("project_features"."issues_access_level" = 10
AND EXISTS
(SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 4156052
AND (project_authorizations.project_id = projects.id)
AND (project_authorizations.access_level >= 10))))
AND "issues"."created_at" <= '2019-08-01'
AND "issues"."created_at" >= '2019-05-01'
AND "labels"."id" IN (2731248,
10230929,
4116705,
2492649,
2278648,
2779806)
GROUP BY "labels"."id",
DATE("issues"."created_at")
Screenshots
Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry -
Documentation created/updated or follow-up review issue created -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Performance and Testing
We are using the standard finders that are executing additional permission checks for the current user. Since we are exposing only counts (and the page will be only visibly by the reporter) I'm not sure if we actually need to do the permission checks.
Closes #32454 (closed)
Edited by Michael Kozono