By dman on

I'm working with a lot of imported content (again) and the client has nicely fed in lots and lots of pages.
But to now impose a bit of structure on the site, I'd like to find the lost pages that never got assigned to any subject at all.
I thought it would be an easy query, but the more I thought about it the more lost I got.
I was going to add the option to the content admin filter - but it's not going to fit, is it?

Any SQL guru got a quick hint?

While we are at it, how about selecting all nodes that do not yet correspond to any menu item?

I'm sure I've got some orphans here.

.dan.

⋅ Categories: Drupal 4.7.x

Comments

robertdouglass’s picture

robertdouglass commented

Categories == taxonomy.module or category.module?

can you use some LEFT JOIN a on a.id = b.id WHERE b.id IS NULL?

- Robert Douglass

-----
Lullabot | My Drupal book | My Digg stories

dman’s picture

dman commented

... I knew I had to avoid that terminology :-}

.dan.
How to troubleshoot Drupal | http://www.coders.co.nz/

.dan. is the New Zealand Drupal Developer working on Government Web Standards

Island Usurper’s picture

Island Usurper commented

I recently had to do just this query for a module I'm working on:

db_query("SELECT * FROM {node} WHERE nid NOT IN (
    SELECT nid FROM {term_node} AS tn 
        LEFT JOIN {term_data} AS td ON tn.tid = td.tid WHERE td.vid = %d)
 AND type = 'product'", $vocab_id)

This query finds all the nodes that do not have any terms in the vocabulary whose id is $vocab_id.

For the menu thing, I'm not as sure about, but you might want to try searching for each node's path in {menu} in a similar manner.

dman’s picture

dman commented

... that looks like what I wanted. I wasn't very fresh in the head about the 'NOT IN' clause.
I was thinking 'IS NULL', but that seemed to be, well, not getting through phase one.

Thanks heaps, I'll give it a go.

.dan.
How to troubleshoot Drupal | http://www.coders.co.nz/

.dan. is the New Zealand Drupal Developer working on Government Web Standards

dman’s picture

dman commented

I've tidied and pasted it as a PHP snippet for reference.
Thanks heaps!

// An admin/content management utility.
// Make sure that everything is tagged with at least one term from the vocab
// SQL thanks to Island Usurper.

$vocab_id = 3;
$content_type = "page";

$result = db_query(
   "SELECT * FROM {node} WHERE nid NOT IN (
    SELECT nid FROM {term_node} AS tn 
    LEFT JOIN {term_data} AS td ON tn.tid = td.tid 
    WHERE td.vid = %d)
    AND type = '%s'",
    $vocab_id , $content_type
);

$list = array();
while ($anode = db_fetch_object($result)) {
  $list[] = l($anode->title, "node/$anode->nid") . " " . l('[edit]', "node/$anode->nid/edit") ;
}

print theme('item_list',$list,"Pages with no classification at all");

.dan.
How to troubleshoot Drupal | http://www.coders.co.nz/

.dan. is the New Zealand Drupal Developer working on Government Web Standards