wpdb select query to get certain meta_key?

I have the below code where I am trying to get a list of orders.

So from the wcomm (woocommerce_order_itemmeta) table, I only want to display the meta_value where the meta_key is equal to _qty or _product_id and I need the _product_id to be filtered on a single value like I have. I can’t seem to make it only do both things.. My output never shows the _qty value because I’m specifically filtering the value of _product_id but I don’t understand what to change

global $wpdb;
$wpdb->woocommerce_order_items = $wpdb->prefix . 'woocommerce_order_items';
$wpdb->woocommerce_order_itemmeta = $wpdb->prefix . 'woocommerce_order_itemmeta';

$uvuorderposts = $wpdb->get_results( 
    "
    SELECT p.ID, p.post_title, p.post_date, wcom.order_item_name, wcomm.meta_key, wcomm.meta_value
    FROM $wpdb->posts AS p
    INNER JOIN $wpdb->postmeta AS pm ON p.ID = pm.post_id
    INNER JOIN $wpdb->woocommerce_order_items AS wcom ON p.ID = wcom.order_id
    INNER JOIN $wpdb->woocommerce_order_itemmeta AS wcomm ON wcom.order_item_id = wcomm.order_item_id
    WHERE (p.post_type = 'shop_order')
    AND (pm.meta_key = '_wc_authorize_net_aim_charge_captured')
    AND (pm.meta_value = 'yes')
    AND (wcomm.meta_key = '_product_id')
    OR (wcomm.meta_key = '_qty')
    AND (wcomm.meta_value = '2193')
    ORDER BY p.post_date
    "
);

echo "<table>";
foreach ( $uvuorderposts as $uvuorderpost ) 
{
    echo "<tr><td>" . $uvuorderpost->post_title . " - " . $uvuorderpost->ID . "</td><td>" . $uvuorderpost->post_date . "</td><td>" . $uvuorderpost->meta_key . ": " .  $uvuorderpost->meta_value . "</td></tr>";
    echo "<tr bgcolor='#eaeaea'><td colspan='3'>" . $uvuorderpost->order_item_name . "</td></tr>";
}
echo "</table>";

I tried to “filter” it on the output by using $uvuorderpost->meta_key[‘_qty’] but it did not work!


Source: wpdb

Leave a Reply