I often run one off reports for clients directly from the MySQL database behind their (normally WordPress) websites. Most are straightforward, perhaps requiring data from the wp_posts and wp_postmeta tables. However it get's a lot more complicated if term data is needed too. This is because determining which terms belong to an object, what taxonomy they are, and what the human readable values are requires accessing 3 seperate database tables.
I'm going to step you through an example that generates a list of WooCommerce products with thier title, SKU, price and categories. You can skip the the full code if you don't want the explanation. While this example is based around WooCommerce, it would equally apply to creating a list of and post type with meta and taxonomy data.
For each product we are going to be retrieving:
- post_title from the wp_posts table
- meta_value from the wp_postmeta table where the meta_key is _sku
- meta_value from the wp_postmeta table where the meta_key is _regular_price
- name value(s) from the wp_terms table
To start with, let's get the Product names – this is the
post_title field. We'll also restrict it to published Products.
SELECT wp_posts.post_title FROM wp_posts WHERE wp_posts.post_type = 'product' AND wp_posts.post_status = 'publish'
SKU and Price
The SKU and Price can be pulled in from the wp_postmeta table. To make sure that we only get the postmeta values we need we'll use a
LEFT JOIN that matches the
ID column from
wp_posts to the
post_id column, and restrict it to values where the
meta_key is the one we are looking for. This is done once for each piece of meta.
SELECT wp_posts.post_title wp_postmeta1.meta_value wp_postmeta2.meta_value FROM wp_posts LEFT JOIN wp_postmeta wp_postmeta1 ON wp_postmeta1.post_id = wp_posts.ID AND wp_postmeta1.meta_key = '_sku' LEFT JOIN wp_postmeta wp_postmeta2 ON wp_postmeta2.post_id = wp_posts.ID AND wp_postmeta2.meta_key = '_regular_price' WHERE wp_posts.post_type = 'product' AND wp_posts.post_status = 'publish'
You can see that the
LEFT JOIN is also naming the table during the join as wp_postmeta1/2 – this means that we can reference them seperately in the
This is where it gets a bit more complex. As before,
LEFT JOINs are used to access specific data from other tables –
First we match
wp_term_relationships against the
ID of each post. This allows access to the
term_taxonomy_id for each term assigned to each product.
LEFT JOIN wp_term_relationships ON wp_term_relationships.object_id = wp_posts.ID
Next we match the
term_taxonomy_id against the same column of the
wp_term_taxonomy table so that we can access the relevant
term_id. Additionally we restrict it to terms that belong to the right taxonomy –
LEFT JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id AND wp_term_taxonomy.taxonomy = 'product_cat'
Finally we can match that
term_id against the
term_id in the
wp_terms table which gives us access to the human readable name of each term.
LEFT JOIN wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id
SELECT statement can now be extended to get the term names with
Putting that all together gives us:
SELECT wp_posts.post_title, wp_postmeta1.meta_value, wp_postmeta2.meta_value, wp_terms.name FROM wp_posts LEFT JOIN wp_postmeta wp_postmeta1 ON wp_postmeta1.post_id = wp_posts.ID AND wp_postmeta1.meta_key = '_sku' LEFT JOIN wp_postmeta wp_postmeta2 ON wp_postmeta2.post_id = wp_posts.ID AND wp_postmeta2.meta_key = '_regular_price' LEFT JOIN wp_term_relationships ON wp_term_relationships.object_id = wp_posts.ID LEFT JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id AND wp_term_taxonomy.taxonomy = 'product_cat' LEFT JOIN wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id WHERE wp_posts.post_type = 'product' AND wp_posts.post_status = 'publish'
We have all the data now, but the returned results are very messy. There will be multiple rows for each Product if they have more than one category, and they are in no particular order.
Sorting out the ordering is as simple as
ORDER BY wp_posts.post_title ASC. To aggregate the rows we can use
GROUP BY wp_posts.ID, but this then means that only 1 category will ever be shown. To solve that we can use the
GROUP_CONCAT operator. Combining it with
ORDER BY and supplying the optional
SEPERATOR allows the categories to appear in a single column, in alphabetical order, and seperated with a comma and space.
GROUP_CONCAT( wp_terms.name ORDER BY wp_terms.name SEPARATOR ', ' )
The complete code
Remember – this is useful for ad hoc generation. If you want to retrieve this data regularly – whether to show it on the front end of the site or in the admin area, you should atleast use the native WordPress functions for accessing the database, and potentially take it from database queries to WP_Query and related abstrations.