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 justgravityform
(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.
Leave a Reply