Tell us what you are trying to do?
Hello I'd like to display the total orders sum and the total amount of customers of all completed orders on the product page.
I've been following the documentation (link down below) to display the individual customers and their order sum on the product pages.
However now I'd like to output the total orders sum and the total amount of customers on each product page.
How is this possible?
Mathematical calculations of View results like these summations would require custom code, unfortunately. If by total order sum you mean total number of products sold, then you can access that value in postmeta for each product using get_post_meta($product_id, 'total_sales', true). However, if you mean the number of orders that include this product, then you'll have to create a custom shortcode and query to determine this. You'll have to query all completed orders (post type 'shop_order', post status 'wc-completed') joined on wp_woocommerce_order_itemmeta product_id. Similarly for a customer count, you'll have to query all completed orders with a unique _customer_user meta value.
I found this code. Does this shortcode allow me to filter the products with the most sales via a toolset views?
function get_product_total_sales( $atts, $content = null ) {
global $wpdb;
extract( shortcode_atts( array(
'productid' => get_the_ID(),
), $atts, 'total_spent_single_product' ) );
$total_sales = $wpdb->get_var( "SELECT SUM( order_item_meta__line_total.meta_value) as order_item_amount
FROM {$wpdb->posts} AS posts
INNER JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON posts.ID = order_items.order_id
INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS order_item_meta__line_total ON (order_items.order_item_id = order_item_meta__line_total.order_item_id)
AND (order_item_meta__line_total.meta_key = '_line_total')
INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS order_item_meta__product_id_array ON order_items.order_item_id = order_item_meta__product_id_array.order_item_id
WHERE posts.post_type IN ( 'shop_order' )
AND posts.post_status IN ( 'wc-completed' ) AND ( ( order_item_meta__product_id_array.meta_key IN ('_product_id','_variation_id')
AND order_item_meta__product_id_array.meta_value IN ('{$productid}') ) );" );
return wc_price( $total_sales );
}
add_shortcode('product_total_sales', 'get_product_total_sales');
It looks like this shortcode allows you to get the total value of sales for a specific product and display that total on your site in terms of your site's currency specifications. I don't think this will help you filter or sort anything in a View. You can use the filter wpv_filter_query to programmatically modify a query filter: https://toolset.com/documentation/programmer-reference/views-filters/#wpv_filter_query
Hm...so are you saying I could use this shortcode with the wpv_filter_query filter to sort the products with the most sales on the frontend?
No, this shortcode won't directly help you sort the results of a View on the front end, because there's no way to use it to sort a View. There are basically 3 ways to sort results:
1. Predefine sort order in a View's Query Filter settings.
2. Create a sortable table from the results of a View and allow Users to click the table headers or use sorting inputs to select their own sorting criteria.
3. Modify the WP Query using wpv_filter_query to programmatically set the order criteria.
1 and 2 are not possible, because you cannot sort based on a shortcode or custom function. You must select from an existing custom field, taxonomy term, or other predefined value. So the only other option is #3, but even WP Query doesn't allow you to sort directly on the results of a shortcode or custom function: https://codex.wordpress.org/Class_Reference/WP_Query#Order_.26_Orderby_Parameters
As you can see, there's not really a way to pass a shortcode or custom function into the orderby parameters. So the best way to handle this type of sorting is indirect. Calculate the total sales value of each product automatically using a cron job, and programmatically insert that value into a new custom field on the Product post type. Then you can sort by the value of that custom field.
The field total_sales on a product indicates the number of that product sold. As far as its relevance here, that's more of a question for WooCommerce. Maybe indirectly? It depends on how/when that number is incremented, whether or not you charge everyone the same price for a product, and probably some other criteria I am not aware of. The field _order_total is related to orders, not products, and is irrelevant here.
I contacted woocommerce about the total_sales custom field which seems to be relevant in my case, however they couldn't tell me when this field is updated because it is outside of there support :/
I did some testing myself but can't really tell. The field seems to get updated even if an order is on hold, however this doesn't make a lot of sense. Shouldn't it only update when the order is completed?
Christian is on vacation. This is Minesh here and I'll take care of this ticket and try to help you further. Hope this is OK.
Well - based on requirement you shared there is no such feature available and it needs custom programming that is eventually beyond the scope of our support policy.
If you need custom programming for your project, Please feel free to contact our certified partners.
=> https://toolset.com/contractors/
