WooCommerce Get All Products With Zero Orders

 

To get all the products with zero orders, use the following code in WooCommerce:

/******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

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