0

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.

I did:

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 :-(

  • Just as a general remark, maybe not for you, I would consider, if it really is necessary to do this with as custom SQL query, because generally, I feel, it is beneficial to work with WordPress' API functions. – Nicolai Jan 19 '16 at 14:52
  • I completely agree, but I couldn't think of a way to use API functions that would produce something that would display the data that I need to pull out, which is a mix of meta key fields from the Woocommerce plugin, and categories ( I also need it to export a .csv and be sortable) If you feel I'm missing something here, please point me! – Victoria Jan 19 '16 at 16:32
  • No worries, you aren't missing something. I'm fairly certain that it is possible - no chance in hell though to explain it in a couple of words. That is the reason why I said general(ly) and formulated as restrictive as I have done in my first comment. At the end I just wanted to note, that there is another way, but that of course is of no immediate help to you. – Nicolai Jan 19 '16 at 18:00
3
+50

SQL-wise, you only need to join to the wp_posts table once. Joining to the terms stuff will give you multiple rows, so it's probably easiest to group these and then use GROUP_CONCAT() to flatten the terms into a comma-separated string (updated to use LEFT joins):

global $wpdb;
$sql = $wpdb->prepare(
    'SELECT p.ID, p.post_title AS Product, pm1.meta_value AS _retail_price, pm2.meta_value AS _stock'
    . ', GROUP_CONCAT(t.name ORDER BY t.name) AS Categories'
    . ' FROM ' . $wpdb->posts . ' p'
    . ' LEFT JOIN ' . $wpdb->postmeta . ' pm1 ON pm1.post_id = p.ID AND pm1.meta_key = %s'
    . ' LEFT JOIN ' . $wpdb->postmeta . ' pm2 ON pm2.post_id = p.ID AND pm2.meta_key = %s'
    . ' LEFT JOIN ' . $wpdb->term_relationships . ' AS tr ON tr.object_id = p.ID'
    . ' LEFT JOIN ' . $wpdb->term_taxonomy . ' AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = %s'
    . ' LEFT JOIN ' . $wpdb->terms . ' AS t ON tt.term_id = t.term_id'
    . ' WHERE p.post_type in (%s, %s) AND p.post_status = %s'
    . ' GROUP BY p.ID'
    . ' ORDER BY p.ID ASC'
    , '_price', '_stock', 'product_cat', 'product', 'product_variation', 'publish'
);
$ret = $wpdb->get_results( $sql );
| improve this answer | |
  • GROUP_CONCAT looks like it might be the way to go. I haven't quite got it working yet, as I do want to do it via the Exports and Reports module as pure MySQL rather than embedding it in a template, as that will make it easier to modify for quick admin reports in future, but I'll accept this answer, as otherwise the bonus would run out of time and nobody get it, which seems unfair, as it's my fault I didn't get back onto this more quickly. – Victoria Jan 25 '16 at 23:51
2

cracks knuckles

Alright -- so first thing is first.

Working w/ WordPress Databases

  1. Read this: https://codex.wordpress.org/Class_Reference/wpdb
  2. Avoid write SQL statements outside of the $wpdb object.

Avoid writing SQL statements like wp_users instead do this:

$users = $wpdb->get_results( "SELECT FROM $wpdb->users" );

This will help if you ever change the database prefix.

Doing this outside of WPDB

Your pseudo code could read like this:

GET POSTS > GET EACH POSTS CATEGORIES > GET EACH POSTS META FIELD NAMED X.

Let's store all the things in something called ... $all_the_things = array();

Getting posts:

<?php
    $arguments = array(
        'posts_per_page'   => -1, // This is a bad idea if you run this on trafficked pages. 
        'post_type'        =>  array('product', 'product_variation'),
        'post_status'      => 'publish'
    );
    $query = WP_Query( $arguments );
?>

Now you need to loop:

<?php
    while( $query->have_posts() ) {
        $categories = get_the_category();
        $price = get_post_meta( get_the_ID(), '_price' ); ?>
        $stock = get_post_meta( get_the_ID(), '_stock' ); ?>
        $all_the_things[get_the_ID()] = array($categories, $price, $stock);
    }
?>

Now if you want... I can do this within SQL -- but it's not ideal. You cannot bank on the database structure staying the exact same, or that filters that should be ran being executed on data and so on. Things can get complicated.

Let me know how this works out and I can provide more detail if needed.

| improve this answer | |
  • I'm really sorry to have not replied to this sooner, I should have posted it when I could be sure to be able to come back and read the answers more promptly. I do understand potential risk of changing the database prefix, and yes, I would normally use wpdb, but for this sort of use, I am using Exports and Reports to produce reports that are very business-specific. So, really I do want to do this within MySQL. In PHP, I'd have to write a CSV exporter and table formatter, and I was hoping to better understand the MySQL approach. – Victoria Jan 25 '16 at 22:09
  • @Victoria alrighty - I'll write some tricks here in a bit – Sterling Hamilton Jan 26 '16 at 4:17
0

You can put together a very finite query with the get_posts() function. I tried to understand your SQL query and define it the way I would with native WP functionality, but could have missed some details. I have no clue if this is where you are going, or if this works, but WordPress is supposed to make things easier for you. Explore this page for more info. Hope this gets you closer to your goal with more ease! Ideally this gets all the possible posts you need to put in columns, and you could sort the $posts as needed.

// Argument array for get_posts
$args = array(
    'post_type' => array(
        'product',
        'product_variation',
    ),
    'meta_query' => array(
        'relation' => 'OR',
        array('key' => '_price'),
        array('key' => '_stock'),
    ),
    'tax_query' => array(
        array(
            'taxonomy' => 'product_cat',
        )
    )
);

// The posts delivered
$posts = get_posts( $args );
| improve this answer | |
  • This might be the way to do it if I were producing a public page for a mass audience, but I'm not. I should have been clearer about that, sorry. I am trying to produce an exportable, easily sortable report for internal use, and I'll probably need to adapt it repeatedly for different scenarios once I've got my head around pulling in the categories. For this reason, I'd like to do it in MySQL rather than php. – Victoria Jan 25 '16 at 23:45
  • I am guessing you don't have the_query available? Not sure how a sql statement is easier. – Nathan Powell Jan 26 '16 at 0:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.