Hi Guys,
In WooCommerce, you can get all the products with zero orders.
If you want to fetch all products having no orders placed for those products, you can use the following code snippet:
/******Start of the code******/
<?php
include 'wp-load.php';
global $wpdb;
$srNo = 1;
$orderSkus = array();
$catalogSkus = array();
$orderSkuResults = $wpdb->get_results("SELECT meta_value FROM ".$wpdb->prefix."term_relationships` as wtr LEFT JOIN ".$wpdb->prefix."posts as wp ON wtr.object_id=wp.ID LEFT JOIN ".$wpdb->prefix."postmeta as wpm ON wp.ID=wpm.post_id WHERE wp.post_type='shop_order' AND wpm.meta_key='_sku' AND `term_taxonomy_id` = ( SELECT term_taxonomy_id FROM `".$wpdb->prefix."term_taxonomy` as wtt LEFT JOIN ".$wpdb->prefix."terms as wt ON wt.term_id=wtt.term_id WHERE wt.slug='simple')", ARRAY_A);
foreach ($orderSkuResults as $orderSkuResult) {
$orderSkus[] = $orderSkuResult['meta_value'];
}
$catalogSkuResult = $wpdb->get_results("SELECT ID,post_title,meta_value,post_modified,post_status FROM `".$wpdb->prefix."term_relationships` as wtr LEFT JOIN ".$wpdb->prefix."posts as wp ON wtr.object_id=wp.ID LEFT JOIN ".$wpdb->prefix."postmeta as wpm ON wp.ID=wpm.post_id WHERE wp.post_type='product' AND `post_status` = 'publish' AND wpm.meta_key='_sku' AND `term_taxonomy_id` = ( SELECT term_taxonomy_id FROM `".$wpdb->prefix."term_taxonomy` as wtt LEFT JOIN ".$wpdb->prefix."terms as wt ON wt.term_id=wtt.term_id WHERE wt.slug='simple')", ARRAY_A);
$catalogDetails = array();
foreach ($catalogSkuResult as $productDetails) {
$catalogDetails[$productDetails['meta_value']]['ID'] = $productDetails['ID'];
$catalogDetails[$productDetails['meta_value']]['post_title'] = $productDetails['post_title'];
$catalogDetails[$productDetails['meta_value']]['post_modified'] = $productDetails['post_modified'];
$catalogDetails[$productDetails['meta_value']]['post_status'] = $productDetails['post_status'];
$catalogSkus[] = $productDetails['meta_value'];
}
$SKUsWithZeroOrders = array_diff($catalogSkus, $orderSkus);
?>
<h1 class="wp-heading-inline"><?php echo esc_html('Products With Zero Orders'); ?></h1>
<hr class="wp-header-end"><br>
<table id="zo_products" cellpadding="5" cellspacing="5" width="100%">
<thead>
<tr>
<th width="3%" style="text-align:center">Sr. No.</th>
<th width="9%" style="text-align:center">Product ID</th>
<th width="11%">Type</th>
<th width="15%">SKU</th>
<th width="31%">Product Name</th>
</tr>
</thead>
<tbody>
<?php
foreach ($SKUsWithZeroOrders as $row) {
$entityID = $catalogDetails[$row]['ID'];
$prdName = $catalogDetails[$row]['post_title'];
$type = 'simple';
?>
<tr>
<td style="text-align:center"><?php echo $srNo; ?></td>
<td style="text-align:center"><?php echo $entityID; ?></td>
<td style="text-align:center"><?php echo ucwords(strtolower($type)); ?></td>
<td style="text-align:center"><?php echo $row; ?></td>
<td><?php echo $prdName; ?></td>
</tr>
<?php $srNo++;
} ?>
</tbody>
</table>
/******End of the code******/
That’s it you are done!
