Search the latest revisions of node body fields

Last updated August 27, 2010. Created by cwebster on August 27, 2010.
Log in to edit this page.

Why

Drupal stores all revisions of a node's body field in the node_revisions table. Unfortunately, this means that if you were to search for a string of text in this table, you would retrieve all revisions—past and present—of each node that contains your search string. This can be frustrating if you're only concerned with the current content of your site, regardless of what it used to be in the past.

This code will help you to search for latest revision only, excluding past revisions.

How

SELECT * FROM node LEFT JOIN node_revisions ON node.vid = node_revisions.vid WHERE node_revisions.body LIKE "%YOUR-SEARCH-TERM%"

This example returns the data for the latest revisions of nodes that have "YOUR-SEARCH-TERM" in their body field.