Add tag to query with hook_query_TAG_alter and filter for moderation state

EntityTypeManager does not allow for filtering through moderation states, although it is in the works for Drupal 11: https://www.drupal.org/project/drupal/issues/3025164#comment-13221005. 

So if you don't want to patch core, here's how to alter your entity query for moderation states using hook_query_TAG_alter:

First add the hook funtion in your custom_module.module file:

  /**
   * Implements hook_query_TAG_alter().
   */
  function custom_module_query_moderation_state_tag_alter(&$query) {
    $query->addJoin('LEFT', 'content_moderation_state_field_data', 'md', 'md.content_entity_revision_id = base_table.vid');
    $query->condition('md.moderation_state', 'archived', '<>');
    $query->condition('md.moderation_state', 'publicly_archived', '<>');
  }

Then add the tag you named in the hook function to your query:

   $storage = $this->entityTypeManager->getStorage('node');
   $query = $storage->getQuery()
      ->latestRevision()
      ->condition('type', ['basic_page', 'landing_page'], 'IN')
      ->condition('status', 1)
      ->accessCheck(FALSE)
      ->addTag('moderation_state_tag')
      ->execute();