I am trying to produce a list of posts and display a report which shows both some meta_values for each post, but also a list of all categories to which each post is attached.
I am using the nifty Exports and Reports module to format the results of my query, and the data I am trying to pull out is a list of Woocommerce products.
I got as far as:
SELECT wp_posts.ID, wp_posts.post_title AS Product, m2.meta_value AS _retail_price, m4.meta_value AS _stock FROM wp_posts LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_price' LEFT JOIN wp_postmeta AS m4 ON m4.post_id = wp_posts.ID AND m4.meta_key = '_stock' WHERE wp_posts.post_type IN ('product', 'product_variation') AND wp_posts.post_status = 'publish' ORDER BY wp_posts.ID ASC
That works, I get a list of posts (products) with the relevant meta values populated.
But I would now like to add a list of categories that each post is listed in, ideally with a column per category. But each post is in multiple categories, and I am stumped as to how to pull out the categories for each one.
SELECT wp_posts.ID AS ProdId, wp_posts.post_title AS Product, m1.meta_value AS _retail_price, m2.meta_value AS _stock, wp_term_relationships.*, wp_terms.* FROM wp_posts, wp_term_relationships, wp_terms LEFT JOIN wp_postmeta AS m1 ON m1.post_id = wp_posts.ID AND m1.meta_key = '_price' LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_stock' LEFT JOIN wp_posts posts2 ON wp_term_relationships.object_id = wp_posts.ID LEFT JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id LEFT JOIN wp_terms terms2 ON wp_terms.term_id = wp_term_relationships.term_taxonomy_id WHERE wp_posts.post_type IN ('product', 'product_variation') AND wp_posts.post_status = 'publish' AND taxonomy = 'product_cat' ORDER BY wp_posts.ID ASC
but that gives me an #1054 - Unknown column 'wp_posts.ID' in 'on clause' and I think I have my joins in a tangle :-(