Finding all Basic Pages not referenced by a main-menu entry

Question

I am trying to produce a list of all published English or no-language Basic Pages on my Drupal 7 website that are not in the main menu hierarchy.

Initially, I tried the technique in Finding all nodes not referenced by a menu entry or another node only to find that the suggested SQL command does not match the current Drupal 7 database structure for menus.

After downloading the Data Dictionary and some trial and error, I came up with the following drush command (with the environment replaced by [redacted]):

drush @[redacted] sqlq "SELECT nid, title FROM node WHERE node.type = 'page' AND node.status = 1 AND (node.language = 'und' OR node.language = 'en') AND CONCAT_WS('node/', CAST(node.nid AS CHAR(6))) NOT IN (SELECT link_path FROM menu_links WHERE menu_links.menu_name = 'main-menu') ORDER BY nid"

Unfortunately, this command is returning all published English or no-language Basic Pages on my Drupal 7 website, not just those which are not in the main menu.

After testing some variations, I suspect that "CONCAT_WS('node/', CAST(node.nid AS CHAR(6)))" is the problem but have no idea how to fix it. Replacing CONCAT_WS with CONCAT does not help.

All Basic Pages on this website have 5 or 6 digits in their node ID. It is okay if I have to use a different statement to get all 5-digit node IDs and all 6-digit node IDs.

Answer 1

I had to wrap the cast in a TRIM function:

drush @[redacted] sqlq "SELECT nid, title FROM node WHERE node.type = 'page' AND node.status = 1 AND (node.language = 'und' OR node.language = 'en') AND CONCAT('node/', TRIM(CAST(node.nid AS CHAR(7)))) NOT IN (SELECT link_path FROM menu_links WHERE menu_links.menu_name = 'main-menu') ORDER BY nid"