Resolve "Can't sort group issues by popularity when searching"

What does this MR do?

Reverts c03386c3, which solves #50246 (closed). I also added some specs to ensure that the feature itself works.

To test this, I re-ran https://gitlab.com/gitlab-org/gitlab-ce/snippets/1721019 with these changes. Before: https://gitlab.com/gitlab-org/gitlab-ce/snippets/1751315 After: https://gitlab.com/gitlab-org/gitlab-ce/snippets/1751318

There are a couple of notable things:

1. A query on the dashboard times out. This isn't great, but it's also hard to optimise the dashboard, and it was failing before.

2. There are a couple of fewer COUNT queries in the second version. I think (but I'm not sure) that's the absence of the CTE simplifying matters.

3. I added a third change, to make the query simpler if the user can see all confidential issues in the group. I don't think this is strictly needed for this bug, but I see this query time out on some DB nodes and not others, while the simplified one doesn't time out on any:

   SELECT "issues".*
   FROM "issues"
   INNER JOIN "projects" ON "projects"."id" = "issues"."project_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 = 443319
           OR EXISTS (
             SELECT TRUE
             FROM issue_assignees
             WHERE user_id = 443319
               AND issue_id = issues.id
             )
           OR issues.project_id IN (
             SELECT "projects"."id"
             FROM "projects"
             INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
             WHERE "project_authorizations"."user_id" = 443319
               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 "id"
       FROM "base_and_descendants" AS "namespaces"
       )
     AND (
       EXISTS (
         SELECT 1
         FROM "project_authorizations"
         WHERE "project_authorizations"."user_id" = 443319
           AND (project_authorizations.project_id = projects.id)
         )
       OR projects.visibility_level IN (
         10,
         20
         )
       )
     AND (
       "project_features"."issues_access_level" IN (
         NULL,
         20
         )
       OR (
         "project_features"."issues_access_level" = 10
         AND EXISTS (
           SELECT 1
           FROM "project_authorizations"
           WHERE "project_authorizations"."user_id" = 443319
             AND (project_authorizations.project_id = projects.id)
           )
         )
       )
     AND (
       "issues"."title" ILIKE '%test%'
       OR "issues"."description" ILIKE '%test%'
       )
   ORDER BY "issues"."id" DESC

Does this MR meet the acceptance criteria?

• Changelog entry added, if necessary
• Tests added for this feature/bug
• Conforms to the code review guidelines
• Conforms to the merge request performance guidelines
• Conforms to the style guides
• Conforms to the database guides

Closes #50246 (closed).