ModerationStateFilter unecessarily joins the content moderation state filter multiple times

Use Case for this bug:

This is a fairly common use case. The default "moderated_content" view provided by Drupal core's content_moderation module has 2 "moderation_state_filter" views filters. The multiple filters do the following:
1. (Exposed) State selection with only Draft and Needs Review as options.
2. (Not exposed) Filters the query to only moderation states that are not Published (ie Draft and Needs Review).

Patch review of #12:

1. Reducing multiple LEFT JOINs to the "content_moderation_state_field_revision" table

Code: core/modules/content_moderation/src/Plugin/views/ModerationStateJoinViewsHandlerTrait.php
Patch change:

--- a/core/modules/content_moderation/src/Plugin/views/ModerationStateJoinViewsHandlerTrait.php
+++ b/core/modules/content_moderation/src/Plugin/views/ModerationStateJoinViewsHandlerTrait.php
@@ -15,6 +15,11 @@ trait ModerationStateJoinViewsHandlerTrait {
    * {@inheritdoc}
    */
   public function ensureMyTable() {
+    // Try to get the alias of an already joined content_moderation_state table.
+    if (!isset($this->tableAlias)) {
+      $this->tableAlias = $this->query->ensureTable('content_moderation_state', $this->relationship);
+    }
+

The above change does not work when the moderation state filter is using a relationship. The "content_moderation_state" is not the table that needs to be ensured. It is the alias used in the "addRelationship()" call later in the "ensureMyTable()" method.
In my opinion, this should check if the relationship exists already with something like the following:

    // Use our existing relationship instead of creating multiple joins.
    if (!isset($this->tableAlias) &&
        !empty($this->query->relationships['content_moderation_state']['base']) &&
        $this->query->relationships['content_moderation_state']['base'] === 'content_moderation_state_field_revision') {
      $this->tableAlias = 'content_moderation_state';
    }

2. Bundles Condition:

Code: core/modules/content_moderation/src/Plugin/views/filter/ModerationStateFilter.php, method "opSimple()"
This code no longer works with the latest Drupal code since the conditions are created differently. I tested on 10.1.
This would need a recursive function in order to find the bundles condition within $this->query->where[$this->options['group']]['conditions']. It could be easier to set a flag on the $this->query that persists between filters, perhaps $this->query->addTag().

3. Reducing multiple "INNER JOIN {node}"

Code: core/modules/content_moderation/src/Plugin/views/filter/ModerationStateFilter.php method "opSimple()"
Before the bundles condition is added, the ModerationStateFilter adds a join to the entity base table (ie "node") in order to ensure that the entity bundle can be filtered. This join also occurs multiple times when there are more than one "moderation_state_filter" added to the view.
A similar relationships check could be used if we use a more unique join alias.
Example:

if ($this->table === $entity_revision_base_table) {
  $base_table_join_alias = "content_moderation_state_base_{$entity_base_table}";
  if (!empty($this->query->relationships[$base_table_join_alias]['base']) &&
      $this->query->relationships[$base_table_join_alias]['base'] === $entity_revision_base_table) {
    // Re-use the existing join.
    $entity_base_table_alias = $base_table_join_alias;
  }
  else {
    // Add a new join.
    $configuration = [ ... ];
    $entity_base_table_alias = $this->query->addRelationship($base_table_join_alias, $join, $entity_revision_base_table);
  }
}

Patch for 11.x.

@_utsavsharma The patch #23 will not work.
1. The Drupal\content_moderation\Plugin\views\filter\Condition is not found. The condition can be created with $this->view->query->getConnection()->condition('AND');

2. As stated in #22 🐛 ModerationStateFilter unecessarily joins the content moderation state filter multiple times Needs work , the bundles condition search needs to be a recursive search. This logic will never be TRUE - if ($condition['field'] === $field && $condition['value'] === $moderated_bundles && $condition['operator'] === 'IN') {