WooCommerce Get All Products With Zero Orders

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!

Leave a Reply