Search WordPress Post and Post Meta

Recently, I had to find instances of where specific Gravity Forms were being used. This came with multiple challenges.

Challenges

Searching for gravityform id="7" returns results for just gravityform (i.e. too many results)
If the gravity form appears in post meta (rather than the primary content), it is excluded (i.e. missing results)

SQL Solution

Find Gravity Form Shortcode in Post Content

This code returns a list of posts (ID and post_title) that contain Gravity Form 7.

SELECT 
    `ID`, 
    `post_title`
FROM `wp_posts` 
WHERE 
    `post_content` LIKE '%[gravityform id="7"%' 
    AND `post_name` NOT LIKE '%-revision-%'
    AND `post_name` NOT LIKE '%-autosave-%';

Find Gravity Form Shortcode in Post Meta

This code returns a list of posts (ID and post_title) that contain Gravity Form 7 in their post meta.

SELECT 
    `wp_postmeta`.`post_id`,
    `wp_posts`.`post_title`
FROM `wp_postmeta`
    LEFT JOIN `wp_posts`
    ON `wp_postmeta`.`post_id`=`wp_posts`.`ID`
WHERE
    `wp_postmeta`.`meta_value` LIKE '%[gravityform id="7"%'
    AND `wp_posts`.`post_name` NOT LIKE '%-revision-%'
    AND `wp_posts`.`post_name` NOT LIKE '%-autosave-%';

WP CLI Solution

Manually wrangling these two separate SQL statements is not very efficient, so I wrote a custom WP CLI command to do this for me. Originally, the command was going to search for a specific Gravity Form shortcut but I realized it was easy to generalize the code to search for any string.

You can find my WP CLI command find-content on GitHub and it can be installed with the command

 wp package install git@github.com:salcode/wp-cli-find-content.git

Example Usage and Output

$ wp find-content '[gravityform id="7"'
+----+-----------------------------+----------+
| ID | permalink                   | location |
+---+------------------------------+----------+
| 1  | http://wp.test/hello-world  | content  |
| 8  | http://wp.test/display-meta | postmeta |
+----+-----------------------------+----------+

Geeky Details

Details – Find Gravity Form Shortcode in Post Content

My first SQL query was

SELECT 
    `ID`, 
    `post_title`
FROM `wp_posts` 
WHERE 
    `post_content` LIKE '%[gravityform id="7"%';

this seemed to work well until I noticed there were too many results.

The problem was I was including revisions and autosaves. The post_name of a revision or autosave looks like the following respectively, 8-revision-v1 and 8-autosave-v1 (where 8 is the original post_id and v1 indicates it is the first version). This allowed me to add some additional WHERE clauses to exclude these posts.

SELECT 
    `ID`, 
    `post_title`
FROM `wp_posts` 
WHERE 
    `post_content` LIKE '%[gravityform id="7"%' 
    AND `post_name` NOT LIKE '%-revision-%'
    AND `post_name` NOT LIKE '%-autosave-%';

This gave me just the posts I want. There were still two drawbacks:

This only lists posts where the shortcode appears in the post content
I don’t have the URL (permalink) for the post

I address finding posts where the shortcode appears in the post meta in the next section.

I don’t have a solution for adding the permalink to this SQL query but I did add it to the WP CLI command I wrote.

Details – Find Gravity Form Shortcode in Post Meta

While I was able to find the Gravity Form Shortcode in the Post Meta with some relatively straight forward SQL

SELECT `post_id`
FROM `wp_postmeta`
WHERE
    `meta_value` LIKE '%[gravityform id="7"%';

this does not address eliminating revisions and autosaves. Since this SQL only queries the wp_postmeta table (and you can only determine if a post is a revision or autosave by querying the wp_posts table), I needed to do an SQL join, specifically a LEFT JOIN. If you’re not familiar with a LEFT JOIN (a.k.a. LEFT OUTER JOIN), I find this Coding Horror: A Visual Explanation of SQL Joins a good place to start.

I rewrote my query to use both tables

SELECT `post_id`
FROM `wp_postmeta`
    LEFT JOIN `wp_posts`
    ON `post_id`=`ID`
WHERE
    `meta_value` LIKE '%[gravityform id="7"%';

which let me then refactor further and exclude revisions and autosaves.

SELECT `wp_postmeta`.`post_id`
FROM `wp_postmeta`
    LEFT JOIN `wp_posts`
    ON `wp_postmeta`.`post_id`=`wp_posts`.`ID`
WHERE
    `wp_postmeta`.`meta_value` LIKE '%[gravityform id="7"%'
    AND `wp_posts`.`post_name` NOT LIKE '%-revision-%'
    AND `wp_posts`.`post_name` NOT LIKE '%-autosave-%';

Details – WP CLI

Rather than wrangling these two separate SQL queries and manually merging the results, I was excited to create my first custom WP CLI command. I look forward to automating more of the work I do. See WP CLI command find-content on GitHub.

Sal Ferrarello (@salcode)

Sal is a PHP developer with a focus on the WordPress platform. He is a conference speaker with a background including Piano Player, Radio DJ, Magician/Juggler, Beach Photographer, and High School Math Teacher. Sal can be found professionally at WebDevStudios, where he works as a senior backend engineer.