WooCommerce Query for products

3 Views

I’m creating a page that is shown when a user add a product in the Cart. Once a user click on “Add to Cart” button, this template is shown.

The goal is to show other products that other customers have bought based on the item added.

Read More

It’s the same functionality than Amazon.

I know how to make queries in WooCommerce but do not know how to get other products contained in other orders that contain the product selected.

Anyone can help me?

Post Views: 3

Related posts

2 comments

  1. Finally I created my own template to show products bought bu other users related to the item added to cart:

    <?php 
/*
 Template Name: template_products_orders
 */

get_header('shop');

global $woocommerce, $wpdb;
$items = $woocommerce->cart->get_cart();
$ids = array();
foreach($items as $item => $values) { 
    $product = $values['data']->post; 
  $ids       = $product->ID; 
} 

$product_selected_id    = $ids;

// Query to get orders with the item selected....
$query           = array();
$query['fields'] = "SELECT DISTINCT ID FROM {$wpdb->posts} p";
$query['join']   = " INNER JOIN wp_woocommerce_order_items items ON items.order_id = p.ID";
$query['join']  .= " INNER JOIN wp_woocommerce_order_itemmeta itemmeta ON (itemmeta.order_item_id = items.order_item_id)";

$query['where']  = " WHERE p.post_type='shop_order' AND (p.post_status='wc-completed' OR p.post_status='wc-pending')
                                                     AND itemmeta.meta_key='_product_id' AND itemmeta.meta_value='$product_selected_id'";

$orders = $wpdb->get_col( implode( ' ', $query ) );

// get items in other orders....
$query           = array();
$query['fields'] = "SELECT itemmeta.meta_value FROM {$wpdb->posts} p";
$query['join']   = " INNER JOIN wp_woocommerce_order_items items ON items.order_id = p.ID";
$query['join']  .= " INNER JOIN wp_woocommerce_order_itemmeta itemmeta ON (itemmeta.order_item_id = items.order_item_id)";
$query['where']  = " WHERE p.ID in (".implode(', ', $orders).")
                                                     AND (itemmeta.meta_key='_product_id' AND itemmeta.meta_value<>'$product_selected_id')";

$products = $wpdb->get_col( implode( ' ', $query ) );

if ( count( $products ) > 0 ) {

    ?>

    <div class="home-featured-products">
        <div class="wrap">
                <section id="text-12">
                        <h3 class="widgettitle">Those who bought this product also purchased</h4>
                        <div class="woocommerce columns-4">
                                <div class="yit-wcan-container">

                                        <?php

                                            $args = array(      'post_type'             => 'product',
                                                                        'post__in'              => $productos,
                                                                        'posts_per_page'    => 4
                                                                    );
                                            $wp_query = new WP_Query($args);

                                        ?>

                                    <ul class="products">

                                    <?php while ($wp_query->have_posts()) : $wp_query->the_post(); ?>

                                        <?php woocommerce_get_template_part('content', 'product'); ?>

                                    <?php endwhile; 
                                    wp_reset_query(); 
                                    ?>

                                        </ul>

                                </div>
                        </div>
                </div>
        </div>
</div>

<?php } ?>

Comments are closed.