Exclude archived projects from group project templates list

What does this MR do and why?

Contributes to #435198 (closed)

Problem

After archiving a project in a group project template subgroup, it is still available for use when creating a new project.

Solution

Apply non_archived scope to group project template query.

Database

Note The execution plan is slow before and after the change. The performance of the endpoint is going to be improved in scope of Performance problem with new projects page (#425166 - closed).

Before

See SQL query

SELECT
    COUNT(*)
FROM
    "projects"
WHERE
    "projects"."namespace_id" IN (WITH "descendants_base_cte" AS MATERIALIZED (
            SELECT
                "namespaces"."id", "namespaces"."traversal_ids"
            FROM
                "namespaces"
                INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
            WHERE
                "members"."type" = 'GroupMember'
                AND "members"."source_type" = 'Namespace'
                AND "namespaces"."type" = 'Group'
                AND "members"."user_id" = 421631
                AND "members"."requested_at" IS NULL
                AND (
                    access_level >= 10
)
                AND (
                    members.access_level >= 30
)
), "superset" AS (
                SELECT
                    d1.traversal_ids
                FROM
                    descendants_base_cte d1
                WHERE
                    NOT EXISTS (
                        SELECT
                            1
                        FROM
                            descendants_base_cte d2
                        WHERE
                            d2.id = ANY (d1.traversal_ids)
                            AND d2.id <> d1.id))
                SELECT DISTINCT
                    "namespaces"."id"
                FROM
                    "superset",
                    "namespaces"
                    INNER JOIN "projects" ON "projects"."namespace_id" = "namespaces"."id"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND next_traversal_ids_sibling ("superset"."traversal_ids") > "namespaces"."traversal_ids"
                    AND "superset"."traversal_ids" <= "namespaces"."traversal_ids"
                    AND "namespaces"."id" IN (
                        SELECT
                            "namespaces"."custom_project_templates_group_id"
                        FROM
                            "namespaces"
                        WHERE
                            "namespaces"."type" = 'Group'
                            AND (traversal_ids[1] IN (
                                    SELECT
                                        "gitlab_subscriptions"."namespace_id"
                                    FROM
                                        "gitlab_subscriptions"
                                    WHERE
                                        "gitlab_subscriptions"."hosted_plan_id" IN (3, 4, 102, 103, 135, 100, 101, 1000335)))
                                AND "namespaces"."custom_project_templates_group_id" IS NOT NULL)
)
AND "projects"."marked_for_deletion_at" IS NULL
AND "projects"."pending_delete" = FALSE;

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24917/commands/79122

After

See SQL query

SELECT
    COUNT(*)
FROM
    "projects"
WHERE
    "projects"."namespace_id" IN (WITH "descendants_base_cte" AS MATERIALIZED (
            SELECT
                "namespaces"."id", "namespaces"."traversal_ids"
            FROM
                "namespaces"
                INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
            WHERE
                "members"."type" = 'GroupMember'
                AND "members"."source_type" = 'Namespace'
                AND "namespaces"."type" = 'Group'
                AND "members"."user_id" = 421631
                AND "members"."requested_at" IS NULL
                AND (
                    access_level >= 10
)
                AND (
                    members.access_level >= 30
)
), "superset" AS (
                SELECT
                    d1.traversal_ids
                FROM
                    descendants_base_cte d1
                WHERE
                    NOT EXISTS (
                        SELECT
                            1
                        FROM
                            descendants_base_cte d2
                        WHERE
                            d2.id = ANY (d1.traversal_ids)
                            AND d2.id <> d1.id))
                SELECT DISTINCT
                    "namespaces"."id"
                FROM
                    "superset",
                    "namespaces"
                    INNER JOIN "projects" ON "projects"."namespace_id" = "namespaces"."id"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND next_traversal_ids_sibling ("superset"."traversal_ids") > "namespaces"."traversal_ids"
                    AND "superset"."traversal_ids" <= "namespaces"."traversal_ids"
                    AND "namespaces"."id" IN (
                        SELECT
                            "namespaces"."custom_project_templates_group_id"
                        FROM
                            "namespaces"
                        WHERE
                            "namespaces"."type" = 'Group'
                            AND (traversal_ids[1] IN (
                                    SELECT
                                        "gitlab_subscriptions"."namespace_id"
                                    FROM
                                        "gitlab_subscriptions"
                                    WHERE
                                        "gitlab_subscriptions"."hosted_plan_id" IN (3, 4, 102, 103, 135, 100, 101, 1000335)))
                                AND "namespaces"."custom_project_templates_group_id" IS NOT NULL)
)
AND "projects"."marked_for_deletion_at" IS NULL
AND "projects"."pending_delete" = FALSE
AND "projects"."archived" = FALSE

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24917/commands/79123

Screenshots or screen recordings

Before	After

How to set up and validate locally

1. Set up subgroup for group project templates
2. Create a template project
3. Archive it
4. Create a new project from template
5. Observe that the archived project is not visible in the list

MR acceptance checklist

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

• I have evaluated the MR acceptance checklist for this MR.