Create a SQL query to display custom fields and custom taxonomies in WordPress
They said the task was simple: Create a sql query to display the custom fields for a custom post type that have a set of specific custom tags so our reporting tool can consume it.
Wait what? That doesn’t sound so simple.
Let’s back up, let me describe the project that I’m currently working on and what the client needed so we can understand what I’m talking about:
Currently I’m working on a WordPress project where we have a custom post type called
student to register, you guessed it… Students. On that custom post type, we have also custom fields to save the students
school_name. Finally, for that custom post type, we created a custom taxonomy called
submission_year since is possible for a student to have multiple records on the
student post type.
Now, let’s say it again: Create a sql query to display the
school_name for the students that have a submitted record for the current year.
Let me show you how I came up with the solution for this report.
- The WordPress Entity Relationship (ER) model
- Understanding the problem
- Starting the query
- Displaying the required fields
- One student per line
The WordPress Entity Relationship (ER) model
If we want to understand the final solution for the requested report, we need to understand the WordPress database structure. Which is actually not that complicated for 2 reasons:
- WordPress has just one table for all the post types like post, pages and images
- If you need to store complex data, like an array or an object. WordPress will serialize that data and store it in a text field on a table.
Here is the current ER for WordPress 5.9, which is the latest one as the time of this writing:
So, instead of having a table and a field for each type of data you want to store like other CMS’s, WordPress reuses the few tables it has to store serialized data.
I’m not saying WordPress does it better, I’m saying that WordPress keeps things simple (which most of the time is better 😏).
Understanding the problem
Now that we know how the WordPress tables relate to one another and what the client wants from us, let’s put into table names terms: We need a query that show the
wp_postmeta.meta_value for all the
wp_post.post_type equal to student where
wp_terms is current year.
The task then is to find a path to join the
wp_postmeta table with the
wp_post table. Easy right?
Starting the query
You would think that the best place to start the query is in the
wp_posts table, since that’s where the student information is right? Wrong! The best place to start this kind of joins, is by finding any Many to Many tables included in the path we just talked about. In our case that would be the
Then we create our first query. A query to join the
SELECT students.post_title FROM wp_term_relationships rel LEFT JOIN wp_term_taxonomy term_tax ON (term_tax.term_taxonomy_id = rel.term_taxonomy_id) LEFT JOIN wp_posts students ON (students.ID = rel.object_id) WHERE term_tax.taxonomy = 'submission_year' AND students.post_status = 'publish' AND students.post_type = 'student'
This query basically will show us all the published students, but with the issue that we still will get multiple submissions for each student. Remember, a student can have multiple submission with different terms (which are the edition year in our case).
- We’re using alias for our tables so our query is more readable
- We’re filtering the posts to only show the students that are published
And the tidbit: The
object_id is the key that joins the
wp_term_relationships with the
wp_post. That one took me by surprise actually.
Displaying the required fields
Until now, we just joined the inner tables required in our query. Lets continue by joining the
wp_terms so we can filter out students without a submission for a specific year and the
wp_postmeta table to actually get the information that we require, like first and last name, zip code, etc.
SELECT students.post_title, meta.meta_key, meta.meta_value FROM wp_term_relationships rel LEFT JOIN wp_term_taxonomy term_tax ON (term_tax.term_taxonomy_id = rel.term_taxonomy_id) LEFT JOIN wp_posts students ON (students.ID = rel.object_id) LEFT JOIN wp_terms sub_year ON (sub_year.term_id = term_tax.term_taxonomy_id) LEFT JOIN wp_postmeta meta ON (meta.post_id = students.ID) WHERE term_tax.taxonomy = 'submission_year' AND students.post_status = 'publish' AND students.post_type = 'student' AND sub_year.slug = '2022' AND meta.meta_key IN ( 'first_name', 'last_name', 'email_address', 'school_zip', 'school_name') ORDER BY students.ID, meta.meta_key
If we execute this query this is what we’ll get:
The problem we have now is that we don’t get a row per student, but a row for every custom field. That’s not good for a reporting tool. Let’s fix that.
One student per line
We could have stopped with our query in the last section. But wouldn’t it be nice if could have one student per row, instead of multiple rows per student?
Well, we can! We just have to change the join to the
wp_postmeta table adding a filtering query, and execute it multiple times:
SELECT students.ID, first_name.meta_value AS first_name, last_name.meta_value AS last_name, email_address.meta_value AS email_address FROM wp_term_relationships rel LEFT JOIN wp_term_taxonomy term_tax ON (term_tax.term_taxonomy_id = rel.term_taxonomy_id) LEFT JOIN wp_posts students ON (students.ID = rel.object_id) LEFT JOIN wp_terms sub_year ON (sub_year.term_id = term_tax.term_taxonomy_id) LEFT JOIN wp_postmeta first_name ON (first_name.post_id = students.ID AND first_name.meta_key = 'first_name' ) LEFT JOIN wp_postmeta last_name ON (last_name.post_id = students.ID AND last_name.meta_key = 'last_name' ) LEFT JOIN wp_postmeta email_address ON (email_address.post_id = students.ID AND email_address.meta_key = 'email_address' ) WHERE term_tax.taxonomy = 'submission_year' AND students.post_status = 'publish' AND students.post_type = 'student' AND sub_year.slug = '2022' ORDER BY first_name.meta_value, last_name.meta_value
And this will result in:
Alas, the report we actually want. And with just one student per line. We’re done!
Take into account that if a student doesn’t have meta, all the fields will be
NULL for that student.
Here I explained what I did to accomplish the task. But if you want to understand what each WordPress table stores, then I suggest this blog post by Sufia Banu.