Finding all nodes not referenced by a menu

Question

I see this question was asked for Drupal 7, but the menu_link table doesn't exist in Drupal 9.

Is there a SQL query for listing all nodes that are not assigned to a menu?

Answer 1

In Drupal 9 can use this 'menu_link_content_data' table to find whether node has any reference as menu:

Snippet code for finding all nodes not referenced by a// Get all nodes.

$nids = \Drupal::entityQuery('node')
      ->execute();
if (isset($nids) && !empty($nids)) {
  foreach($nids as $nid){
    // Check nid has reference as menu
    $rst =  \Drupal::database()->select('menu_link_content_data', 'mn')
      ->fields('mn')
      ->condition('mn.link__uri', 'entity:node/'.$nid, '=')
      ->execute()
      ->fetchAll();
    // If nid has no reference as menu then insert nid in $nodes[]
    if(empty($rst)){
     $nodes[] = $nid;
    }
  }
}

Answer 2

I ended up installing Menu Entity Index https://www.drupal.org/project/menu_entity_index which added a database table and views integration. This allowed me to set up a view listing all nodes of specific content types not in selected menus.

Once that module has been enabled and configured, the table it adds is created. I used the SQL query below.

$query = 'SELECT * FROM `node_field_data` t1
WHERE (t1.status = 1) AND 
    (t1.type = "article"
    OR t1.type = "page"
    OR t1.type = "content_type_x"
    OR t1.type = "webform") AND
NOT EXISTS (SELECT 1 FROM menu_entity_index t2 WHERE t2.target_id = t1.nid)
ORDER BY "node_field_data_created" DESC';

This selects all node data where the content type is article, page, etc. and the node.nid column is not found in the target_id column of the menu entity index table.