ok i have the following:
user id meta
2 _qty 3
2 Weight 20
1 _qty 1
1 weight 30
I need to grab the user id column then multiply the qty by the weight giving me for user id 2= 60 and user id = 1 30……
I’ve tried the following but to no avail:
$myrows = $wpdb->get_results( "SELECT meta_value, SUM(_qty*weight) AS product_id FROM {$wpdb->prefix}woocommerce_order_itemmeta GROUP BY ($query_select_order_items)" );
where ($query_select_order_items)
is the user_id.
how do i go about this?
Actual php I’m currently running:
/**
* Returns all the orders made by the user
*
* @param int $user_id
* @param string $status (completed|processing|canceled|on-hold etc)
* @return array of order ids
*/
function fused_get_all_user_orders($user_id,$status='completed'){
if(!$user_id)
return false;
$orders=array();//order ids
$args = array(
'numberposts' => -1,
'meta_key' => '_customer_user',
'meta_value' => $user_id,
'post_type' => 'shop_order',
'post_status' => 'publish',
/* 'tax_query'=>array(
array(
'taxonomy' =>'shop_order_status',
'field' => 'slug',
'terms' =>$status
)
) */
);
$posts=get_posts($args);
//get the post ids as order ids
$orders=wp_list_pluck( $posts, 'ID' );
return $orders;
}
function fused_get_all_products_ordered_by_user($user_id=false){
$orders=fused_get_all_user_orders($user_id);
if(empty($orders))
return false;
$order_list='('.join(',', $orders).')';//let us make a list for query
//so we have all the orders made by this user which was successfull
//we need to find the products in these order and make sure they are downloadable
// find all products in these order
global $wpdb;
$query_select_order_items="SELECT order_item_id as id FROM {$wpdb->prefix}woocommerce_order_items WHERE order_id IN {$order_list}";
$query_select_product_ids="SELECT meta_value as product_id FROM {$wpdb->prefix}woocommerce_order_itemmeta WHERE meta_key=%s AND order_item_id IN ($query_select_order_items)";
$products=$wpdb->get_col($wpdb->prepare($query_select_product_ids,'weight'));
$qty = $wpdb->get_col($wpdb->prepare($query_select_product_ids,'_qty'));
return $products;
}
Where currently that $products on the end returns a list of weight’s or if i set it to _qty it will return a list of the quantities ordered…
sqlfiddle
Update from strawberries answer:
"SELECT order_item_id, weight * quantity total FROM (
SELECT order_item_id,
MAX(CASE WHEN meta_key = '_qty' THEN meta_value ELSE 0 END) quantity ,
MAX(CASE WHEN meta_key = 'weight' THEN meta_value ELSE 0 END) weight
FROM wp_woocommerce_order_itemmeta GROUP BY order_item_id)
WHERE order_item_id IN ($query_select_order_items) x;"
Here’s one way…
… or even just …
Adapting the first query to your fiddle might look like this…
…and the second might look like this…
… and adapting the first to your latest requirement…
You can use this query :
Also i prepare fiddle of it.
You can try it in this fiddle