Woocommerce Question posted in
The official Woocommerce documentation can be found here.

how to get woocommerce best selling product include product-image and etc… with SQL query?

I need to get best selling product from woocommerce here i have a query that bring me best selling product but there is no data about product images or product URl i need those too.

SELECT * FROM wp_posts  
INNER JOIN wp_postmeta 
ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1 
AND ( wp_postmeta.meta_key = 'total_sales' ) 
AND wp_posts.post_type = 'product' 
AND (wp_posts.post_status = 'publish') 
GROUP BY wp_posts.ID 
ORDER BY wp_postmeta.meta_value+0 
DESC, wp_posts.post_date ASC LIMIT 0, 16

i tried to use “SELECT TO” but mysql does not support this also its not suppirt “IN” !

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    here my solution :

     function best_selling_products(){
        global $wpdb;
// first of all I get IDs of the bestselling product with this query
        $query="SELECT wp_posts.id FROM wp_post

s INNER JOIN wp_postmeta 
ON ( wp_posts.ID = wp_postmeta.post_id ) 
WHERE 1=1 AND ( wp_postmeta.meta_key = 'total_sales' ) 
AND wp_posts.post_type = 'product' 
AND (wp_posts.post_status = 'publish') 
GROUP BY wp_posts.ID 
ORDER BY wp_postmeta.meta_value+0 DESC, wp_posts.post_date ASC LIMIT 0, 16";

//then i used that get whatever that I wand from wp_get_product with product id
    $prdtc=$wpdb->get_results($query);
    $array=array();
    $i=0;
foreach ($prdtc as $id)
 {
        $res = wc_get_product( $id->id);
                $array[$i]=array(
    $id->id,
    $res->get_name(),
    $res->get_price(),
    get_the_post_thumbnail_url($id->id),
    get_permalink($id->id)
);
            $i++;
        }
            echo json_encode($array,JSON_FORCE_OBJECT);
        }

    that's all I hope it's useful for someone.


  2. 0

    You can use this code for retrieving the best selling product in the loop.

    <?php
    $args = array(
        'post_type' => 'product',
        'meta_key' => 'total_sales',
        'orderby' => 'meta_value_num',
        'posts_per_page' => -1,
    );
    $loop = new WP_Query( $args );
    while ( $loop->have_posts() ) : $loop->the_post(); 
    global $product; 
?>
<div>

<a href="<?php the_permalink(); ?>" id="id-<?php the_id(); ?>" title="<?php the_title(); ?>">

<?php 
if (has_post_thumbnail( $loop->post->ID )) 
echo get_the_post_thumbnail($loop->post->ID, 'shop_catalog'); 
else echo '<img src="'.woocommerce_placeholder_img_src().'" alt="product placeholder Image" width="65px" height="115px" />'; 
?>
<h3><?php the_title(); ?></h3>
</a>
</div>
<?php endwhile; ?>
<?php wp_reset_query(); ?>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search