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!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s