Hi I am working on woocommerce
I have added custom fields in Woocommerce for orders which is an Agent name each order is assigned with an Agent.
I would like to get Agent name with its total order count.
I have created custom widget and its working well , just issue with the agent name its coming every time. I need agent name only one time and its total count.
Below is the code I have tried:
function wc_orders_dashboard_widget_function() { $args = array( 'meta_key' =>'_wc_acof_2', 'post_type' => 'shop_order', 'post_status' => 'All', 'posts_per_page' => 5 ); $orders = get_posts( $args ); if( count( $orders ) > 0 ) { ?> <table width="100%" class="vao_orders_table"> <tr> <th><?php _e( 'Agent Name', 'woocommerce' ); ?></th> <th><?php _e( 'Order Count', 'woocommerce' ); ?></th> </tr> <?php foreach ( $orders as $key => $value ) { ?> <tr> <td> <?php $order = new WC_Order( $value->ID ); $order_id = $order->get_order_number(); $agent_name = get_post_meta( $order_id, '_wc_acof_2', true ); // 1. Get the order ID and its link if ( $agent_name ) { echo $agent_name; ?> </td> <td> <?php // 2. Get status of the order $order_count += wp_count_posts($value->post_type ); echo $order_count; } ?> </td> </tr> <?php } ?></table><?php // 4. Adding All orders link which will redirect to Orders page of WooCommerce printf( '<div id="vao_orders"><span class="dashicons dashicons-cart"></span> <a href="%s">' . __( 'View all orders' ) . '</a></div>', admin_url( 'edit.php?post_type=shop_order' ) ); }else{ // If no orders then display No Orders message echo __( 'No Orders.' ); } }
But it displays output like below :
Agent Name Order Count
Agent1 1
Agent1 2
Agent2 3
Agent1 4
My expected output :
Agent Name Order Count
Agent1 3
Agent2 1
Can you guys please help me on this ? What I am missing ?
Advertisement
Answer
This can be done with a very light and simple SQL query, that will compact your function:
function wc_orders_dashboard_widget_function() { global $wpdb; $domain = 'woocommerce'; // The SQL query $results = $wpdb->get_results( " SELECT meta_value as value, count(meta_value) as count FROM {$wpdb->prefix}postmeta WHERE meta_key LIKE '_wc_acof_2' GROUP BY meta_value ORDER BY meta_value " ); if( count( $results ) > 0 ): ?> <table width="100%" class="vao_orders_table"> <tr> <th><?php _e( 'Agent Name', $domain ); ?></th> <th><?php _e( 'Order Count', $domain ); ?></th> </tr> <?php foreach ( $results as $result ): ?> <tr> <td><?php echo $result->value; ?></td> <td><?php echo $result->count; ?></td> </tr> <?php endforeach; ?> </table> <?php // 4. Adding All orders link which will redirect to Orders page of WooCommerce printf( '<div id="vao_orders"> <span class="dashicons dashicons-cart"></span> <a href="%s">%s</a> </div>', admin_url( 'edit.php?post_type=shop_order' ), __( 'View all orders', $domain ) ); else: // If no orders then display No Orders message echo __( 'No Orders.', $domain ); endif; }
Code goes in function.php file of your active child theme (or active theme).
Tested and works.