Views and taxonomy filtering "this" field to "that" field

Built a custom module to do this. It does not seem possible via views.

EDIT: My apologies, I should have added this first time round.

I have a single taxonomy vocabulary. A content type then has two fields referencing this taxonomy vocabulary - display and assign. "Display" lists all the terms that should looked for in the "Assign". the SQL used is as follows:

SELECT node__field_ASSIGN.entity_id AS nid, node_field_data.created AS node_field_data_created FROM node__field_ASSIGN INNER JOIN node__field_DISPLAY ON node__field_ASSIGN.field_ASSIGN_target_id = node__field_DISPLAY.field_DISPLAY_target_id LEFT JOIN node_field_data ON node__field_ASSIGN.entity_id = node_field_data.nid AND node_field_data.status = '1' AND node_field_data.type = 'page' WHERE node__field_DISPLAY.entity_id = THIS_NID ORDER BY node_field_data_created DESC; 

Once you have the sql returning the results you think you should have, build your module.

namespace Drupal\custom_module\Plugin\Block; use Drupal\Core\Block\BlockBase; use Drupal\Core\Database\Database; use Drupal\node\Entity\Node; use Drupal\Core\Url; use Drupal\Core\Link; class Custom_Module extends BlockBase { /** * Generate a clickable link from a title and a URL. * * @param string $title * The link text (title). * @param string $url * The URL (either relative or absolute). * * @return string * The HTML link as a string. */ private function generate_link($title, $url): string { // Create a Url object. $url_object = Url::fromUserInput($url, ['absolute' => FALSE]); // Create the link using the title and Url object. $link = Link::fromTextAndUrl($title, $url_object); // Render the link as a string. return $link->toString(); } /** * Get the title and page link of a node from its node ID. * * @param int $nid * The node ID. * * @return string * An array containing the title and link of the node. */ private function get_node_title_and_link($nid): string { // Load the node entity by its ID. $node = Node::load($nid); if ($node) { // Get the node title. $title = $node->getTitle(); // Get the canonical URL for the node. $url = Url::fromRoute('entity.node.canonical', ['node' => $nid]) ->toString(); $node_uri = 'entity:node/' . $nid; $alias = \Drupal::service('path_alias.manager')->getAliasByPath('/node/' . $nid); // Search for menu links using either the internal URI or the alias. $menu_links = \Drupal::entityTypeManager() ->getStorage('menu_link_content') ->loadByProperties([ 'link.uri' => [$node_uri, 'internal:' . $alias], ]); if (!empty($menu_links)) { $title = ''; foreach ($menu_links as $menu_link) { $title .= $menu_link->getTitle(); } } return $this->generate_link($title, $url); } else { // Return a message if the node doesn't exist. return 'Page not found'; } } /** * {@inheritdoc} */ public function build() { $return = []; $this->routeMatch = \Drupal::service('current_route_match'); if ($this->routeMatch && $this->routeMatch->getRouteName() === 'entity.node.canonical') { $node = $this->routeMatch->getParameter('node'); if ($node) { $nid = $node->id(); // Execute the SQL query: $connection = Database::getConnection(); $query = $connection->select('node__field_ASSIGN', 'rsa') ->fields('rsa', ['entity_id']) ->condition('rsd.entity_id', $nid, '='); // Join other tables as needed. $query->join('node__field_DISPLAY', 'rsd', 'rsa.field_ASSIGN_target_id = rsd.field_DISPLAY_target_id'); $query->leftjoin('node_field_data', 'fd', "rsa.entity_id = fd.nid AND fd.status = '1' AND fd.type = 'page'"); $query->addField('fd', 'created', 'node_created'); $query->orderBy('node_created', 'DESC'); // \Drupal::logger('debug') ->notice('Generated SQL: ' . $query->__toString()); // Print the generated SQL query to Watchdog. $results = $query->execute()->fetchAll(); // Prepare the render array for HMTL output. $html_content = '<ul>'; foreach ($results as $row) { $html_content .= '<li>' . $this->get_node_title_and_link($row->entity_id) .'</li>'; } $html_content .= '</ul>'; return [ '#type' => 'markup', '#markup' => $html_content, '#allowed_tags' => ['ul','li','a'], // Specify allowed HTML tags for security. ]; } } return $return; } // cache block per route public function getCacheContexts() { return ['route']; } }