Fix cross-DB query in EnvironmentStatus

What does this MR do and why?

Fixes cross joins with deployments table for environments_status feature.

We have made use of an alternate approach to fetch the same details using the ci_builds_metadata table.

Current query

SELECT
    "environments".*
FROM
    "environments"
WHERE
    "environments"."id" IN (
        WITH RECURSIVE "base_and_descendants" AS (
            (
                SELECT
                    "ci_pipelines".*
                FROM
                    "ci_pipelines"
                WHERE
                    "ci_pipelines"."id" = 30
            )
            UNION
                (
                    SELECT
                        "ci_pipelines".*
                    FROM
                        "ci_pipelines",
                        "base_and_descendants",
                        "ci_sources_pipelines"
                    WHERE
                        "ci_sources_pipelines"."pipeline_id" = "ci_pipelines"."id"
                        AND "ci_sources_pipelines"."source_pipeline_id" = "base_and_descendants"."id"
                        AND "ci_sources_pipelines"."source_project_id" = "ci_sources_pipelines"."project_id"
                )
        )
        SELECT
            "deployments"."environment_id"
        FROM
            "base_and_descendants" AS "ci_pipelines"
            INNER JOIN "ci_builds" ON "ci_builds"."commit_id" = "ci_pipelines"."id"
            AND "ci_builds"."type" = ' Ci :: Build '
            INNER JOIN "deployments" ON "deployments"."deployable_type" = ' CommitStatus '
            AND "deployments"."deployable_id" = "ci_builds"."id"
    )
    AND ("environments"."state" IN (' available '))

New queries

SELECT DISTINCT "expanded_environment_name"
FROM            "ci_builds"
INNER JOIN      "ci_builds_metadata"
ON              "ci_builds_metadata"."build_id" = "ci_builds"."id"
WHERE           "ci_builds"."type" = 'Ci::Build'
AND             (
                                "ci_builds"."retried" = false
                OR              "ci_builds"."retried" IS NULL)
AND             "ci_builds"."commit_id" IN (WITH recursive "base_and_descendants" AS (
                                            (
                                                   SELECT "ci_pipelines".*
                                                   FROM   "ci_pipelines"
                                                   WHERE  "ci_pipelines"."id" = 96)
                                     UNION
                                           (
                                                  SELECT "ci_pipelines".*
                                                  FROM   "ci_pipelines",
                                                         "base_and_descendants",
                                                         "ci_sources_pipelines"
                                                  WHERE  "ci_sources_pipelines"."pipeline_id" = "ci_pipelines"."id"
                                                  AND    "ci_sources_pipelines"."source_pipeline_id" = "base_and_descendants"."id"
                                                  AND    "ci_sources_pipelines"."source_project_id" = "ci_sources_pipelines"."project_id"))SELECT id
                                      FROM   "base_and_descendants" AS "ci_pipelines")
         AND    "ci_builds_metadata"."expanded_environment_name" IS NOT NULL limit 100

SELECT "projects"."id", "projects"."name", "projects"."path", "projects"."description", "projects"."created_at", "projects"."updated_at", "projects"."creator_id", "projects"."namespace_id", "projects"."last_activity_at", "projects"."import_url", "projects"."visibility_level", "projects"."archived", "projects"."avatar", "projects"."merge_requests_template", "projects"."star_count", "projects"."merge_requests_rebase_enabled", "projects"."import_type", "projects"."import_source", "projects"."approvals_before_merge", "projects"."reset_approvals_on_push", "projects"."merge_requests_ff_only_enabled", "projects"."issues_template", "projects"."mirror", "projects"."mirror_user_id", "projects"."shared_runners_enabled", "projects"."runners_token", "projects"."build_coverage_regex", "projects"."build_allow_git_fetch", "projects"."build_timeout", "projects"."mirror_trigger_builds", "projects"."pending_delete", "projects"."public_builds", "projects"."last_repository_check_failed", "projects"."last_repository_check_at", "projects"."only_allow_merge_if_pipeline_succeeds", "projects"."has_external_issue_tracker", "projects"."repository_storage", "projects"."repository_read_only", "projects"."request_access_enabled", "projects"."has_external_wiki", "projects"."ci_config_path", "projects"."lfs_enabled", "projects"."description_html", "projects"."only_allow_merge_if_all_discussions_are_resolved", "projects"."repository_size_limit", "projects"."printing_merge_request_link_enabled", "projects"."auto_cancel_pending_pipelines", "projects"."service_desk_enabled", "projects"."cached_markdown_version", "projects"."delete_error", "projects"."last_repository_updated_at", "projects"."disable_overriding_approvers_per_merge_request", "projects"."storage_version", "projects"."resolve_outdated_diff_discussions", "projects"."remote_mirror_available_overridden", "projects"."only_mirror_protected_branches", "projects"."pull_mirror_available_overridden", "projects"."jobs_cache_index", "projects"."external_authorization_classification_label", "projects"."mirror_overwrites_diverged_branches", "projects"."pages_https_only", "projects"."external_webhook_token", "projects"."packages_enabled", "projects"."merge_requests_author_approval", "projects"."pool_repository_id", "projects"."runners_token_encrypted", "projects"."bfg_object_map", "projects"."detected_repository_languages", "projects"."merge_requests_disable_committers_approval", "projects"."require_password_to_approve", "projects"."emails_disabled", "projects"."max_pages_size", "projects"."max_artifacts_size", "projects"."remove_source_branch_after_merge", "projects"."marked_for_deletion_at", "projects"."marked_for_deletion_by_user_id", "projects"."autoclose_referenced_issues", "projects"."suggestion_commit_message", "projects"."project_namespace_id" FROM "projects" WHERE "projects"."id" = 20 LIMIT 1

SELECT "environments".* FROM "environments" WHERE "environments"."project_id" = 20 AND "environments"."name" IN ('Staging', 'hello', 'hello3')

Screenshots or screen recordings

Verified locally and screenshots are available in !71894 (comment 701737203)

How to set up and validate locally

> pipeline = Ci::Pipeline.last
> pipeline.environments_in_self_and_descendants

# It should return the environments and the query should not do a cross join with the ci tables.

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.

Related to #340781 (closed)