0

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.

0

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"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.