CODEWITHSUNDEEP
wordpresswoocommerce
Apalabrados
Apalabrados

Reputation: 1146

WooCommerce Query for products

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.

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?

Upvotes: 2

Views: 2755

Answers (2)

Apalabrados
Apalabrados

Reputation: 1146

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 } ?>

Upvotes: 1

Domain
Domain

Reputation: 11808

There is currently no plugin providing this feature as such. But the following link might help you to achieve the purpose you are intending to.

https://github.com/bhaveshbhide/woocommerce-cross-selling/

This is the only useful link i found which has an answer to your question.

Do let me know if this works for you.

Upvotes: 0

Related Questions