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.