Add milestone issues display limit
This reinstates the work done on !23102 (merged) which has been reverted.
It was reverted because it did break users workflow due to low milestone display limit.
Now we raised the milestones display limit to 3000 based on the data on #39453 (comment 304226215)
Also there was a sorting problem which is now fixed.
This is a temporary solution and should be replaced soon by loading the milestones issues async.
For more information check: #197227 (closed)
Query to retrieve project milestone issues
SELECT issues.*,
(
SELECT Min("label_priorities"."priority")
FROM "labels"
LEFT OUTER JOIN "label_priorities"
ON "labels"."id" = "label_priorities"."label_id"
INNER JOIN "label_links"
ON "label_links"."label_id" = "labels"."id"
WHERE (
label_priorities.project_id = issues.project_id)
AND (
label_links.target_id = issues.id)
AND "label_links"."target_type" = 'Issue') AS highest_priority
FROM "issues"
WHERE (
id IN
(
SELECT id
FROM (
SELECT issues.*,
(
SELECT Min("label_priorities"."priority")
FROM "labels"
LEFT OUTER JOIN "label_priorities"
ON "labels"."id" = "label_priorities"."label_id"
INNER JOIN "label_links"
ON "label_links"."label_id" = "labels"."id"
WHERE (
label_priorities.project_id = issues.project_id)
AND (
label_links.target_id = issues.id)
AND "label_links"."target_type" = 'Issue') AS highest_priority
FROM "issues"
WHERE "issues"."project_id" = 1
AND "issues"."milestone_id" = 1
GROUP BY "issues"."id"
ORDER BY highest_priority ASC nulls last,
"issues"."id" DESC limit 3000) AS limited_ids))
GROUP BY "issues"."id"
ORDER BY highest_priority ASC nulls last,
"issues"."id" DESC
related to #39453 (closed)Query to retrieve group milestone issues
SELECT issues.*,
(
SELECT Min("label_priorities"."priority")
FROM "labels"
LEFT OUTER JOIN "label_priorities"
ON "labels"."id" = "label_priorities"."label_id"
INNER JOIN "label_links"
ON "label_links"."label_id" = "labels"."id"
WHERE (
label_priorities.project_id = issues.project_id)
AND (
label_links.target_id = issues.id)
AND "label_links"."target_type" = 'Issue') AS highest_priority
FROM "issues"
WHERE (
id IN
(
SELECT id
FROM (
SELECT issues.*,
(
SELECT Min("label_priorities"."priority")
FROM "labels"
LEFT OUTER JOIN "label_priorities"
ON "labels"."id" = "label_priorities"."label_id"
INNER JOIN "label_links"
ON "label_links"."label_id" = "labels"."id"
WHERE (
label_priorities.project_id = issues.project_id)
AND (
label_links.target_id = issues.id)
AND "label_links"."target_type" = 'Issue') AS highest_priority
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" = 23)
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" = 1
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"."milestone_id" = 3
GROUP BY "issues"."id"
ORDER BY highest_priority ASC nulls last,
"issues"."id" DESC limit 3000) AS limited_ids))
GROUP BY "issues"."id"
ORDER BY highest_priority ASC nulls last,
"issues"."id" DESC