Top labels endpoint for type of work chart
What does this MR do?
Endpoint to expose top labels for MRs or Issues for pre-populating the labels for type of work chart.
Note 1: For performance reasons we calculate the top 100 labels by inspecting the last 100 MRs or Issues.
Note 2: The change is not visible to the end user ATM, the FE needs to call the endpoint.
Screenshots
Queries
Find the top 10 labels assigned to the last 100 MRs or Issues (scope is coming from the finder classes).
Issues query:
SELECT DISTINCT labels.*,
Count("labels"."id") OVER (partition BY "labels"."id") AS count_by_id
FROM "labels"
INNER JOIN "label_links"
ON "label_links"."label_id" = "labels"."id"
WHERE "labels"."type" = 'GroupLabel'
AND "label_links"."target_type" = 'Issue'
AND "label_links"."target_id" IN
(
SELECT "issues"."id"
FROM "issues"
INNER JOIN "projects"
ON "projects"."id" = "issues"."project_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" = 'Group'
AND "namespaces"."id" = 9970)
UNION
(
SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = '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))
OR projects.visibility_level IN (0,10,20))
AND (
"project_features"."issues_access_level" > 0
OR "project_features"."issues_access_level" IS NULL)
AND "issues"."created_at" <= '2020-02-06 23:59:59.999999'
AND "issues"."created_at" >= '2019-12-07 00:00:00'
ORDER BY "issues"."id" DESC limit 100)
ORDER BY "count_by_id" DESC limit 10Merge request query:
SELECT DISTINCT labels.*,
Count(labels.id) OVER (partition BY labels.id) AS count_by_id
FROM "labels"
INNER JOIN "label_links"
ON "label_links"."label_id" = "labels"."id"
WHERE "labels"."type" = 'GroupLabel'
AND "label_links"."target_type" = 'MergeRequest'
AND "label_links"."target_id" IN
(
SELECT "merge_requests"."id"
FROM "merge_requests"
INNER JOIN "projects"
ON "projects"."id" = "merge_requests"."target_project_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" = 'Group'
AND "namespaces"."id" = 9970)
UNION
(
SELECT "namespaces".*
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = '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))
OR projects.visibility_level IN (0,10,20))
AND (
"project_features"."merge_requests_access_level" > 0
OR "project_features"."merge_requests_access_level" IS NULL)
AND "merge_requests"."created_at" <= '2020-02-06 23:59:59.999999'
AND "merge_requests"."created_at" >= '2020-01-07 00:00:00'
ORDER BY "merge_requests"."id" DESC limit 100)
ORDER BY "count_by_id" DESC limit 10;Does this MR meet the acceptance criteria?
Conformity
- [-] Changelog entry
- [-] 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
Related to #196582 (closed)
Edited by 🤖 GitLab Bot 🤖