Woocommerce - Changing the order of the upsell products

Having some trouble with a clients site.

We have a product with four upsell products linked, and they display on the site. Only problem is, those products are listed on the page in a seemingly random order (IE, no correlation between ID's, post dates, names etc).

What we want, is for those products to be shown, as inputted in the back end.

This is currently the code set up we currently have going on (as far as I'm aware, this is vanilla WC, with one addition I will point out.

?php
/**
 * Single Product Up-Sells
 *
 * @author      WooThemes
 * @package     WooCommerce/Templates
 * @version     1.6.4
 */

if ( ! defined( 'ABSPATH' ) ) exit; // Exit if accessed directly

global $product, $woocommerce_loop;

$upsells = $product-get_upsells();

if ( sizeof( $upsells ) == 0 ) return;

$meta_query = WC()-query-get_meta_query();

$args = array(
    'post_type'           = 'product',
    'ignore_sticky_posts' = 1,
    'no_found_rows'       = 1,
    'posts_per_page'      = $posts_per_page,
    'orderby'             = 'post__in',
    'order'               = 'asc',
    'post__in'            = $upsells,
    'post__not_in'        = array( $product-id ),
    'meta_query'          = $meta_query
);

$products = new WP_Query( $args );

$woocommerce_loop['columns'] = $columns;

if ( $products-have_posts() ) : ?

div class=panel panel-default
    div class=panel-heading
        h2 class=panel-title?php _e( 'You may also likehellip;', 
'woocommerce' ) ?/h2
    /div
    div class=panel-body

        ?php woocommerce_product_loop_start(); ?

            ?php while ( $products-have_posts() ) : $products-the_post(); ?

                ?php wc_get_template_part( 'content', 'product' ); ?

            ?php endwhile; // end of the loop. ?

        ?php woocommerce_product_loop_end(); ?

    /div
/div

?php endif;

wp_reset_postdata();

It seems that the query that gets run from this query is wrong. (I'm not amazingly versed in the ways of SQL, nor the structure of the WP database)

These args currently products this SQL query

$args = array(
'post_type'           = 'product',
'ignore_sticky_posts' = 1,
'no_found_rows'       = 1,
'posts_per_page'      = $posts_per_page,
'orderby'             = 'post__in',
'order'               = 'asc',
'post__in'            = $upsells,
'post__not_in'        = array( $product-id ),
'meta_query'          = $meta_query
);

SELECT wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1 AND wp_posts.ID IN (8487,8522,8478,11028) AND ( ( wp_postmeta.meta_key = '_visibility' AND wp_postmeta.meta_value IN ('visible','catalog') ) ) AND wp_posts.post_type = 'product' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.menu_order, FIELD( wp_posts.ID, 8487,8522,8478,11028 ) LIMIT 0, 100

Tried so far

  • Changing 'orderby' to both 'post__in' and 'menu_order'
  • Changing 'order' to both 'asc', 'desc', and removing completely
  • Creating an array of id's and pass that new array into 'post__in'
  • Removing 'post__not_in'

I know this is off topic, via the woocommerce tag, but I'm hoping the core issue is with the WP query (I can't imagine it being anything WC specific). If not, I'm more than happy to relocate.

Cheers!

Topic menu-order woocommerce-offtopic wp-query Wordpress sql

Category Web

1
answered at

Getting too long for a comment, sooo in my super simple test overriding up-sells.php on a fresh WC install with a WP_Query, I can change the orderby and order parameters and the products display in the order of my array of product ids:

$meta_query = WC()->query->get_meta_query();

$my_upsells = [171, 170, 172];

$args = array(
    'post_type'           => 'product',
    'ignore_sticky_posts' => 1,
    'no_found_rows'       => 1,
    'posts_per_page'      => $posts_per_page,
    'orderby'             => 'post__in',
    'order'               => 'asc',
    'post__in'            => $my_upsells,
    'meta_query'          => $meta_query
);

$products = new WP_Query( $args );

while( $products->have_posts() ) : $products->the_post();
    echo get_the_title() . "<br>";
endwhile;

One thing to note, WordPress does say that you cannot combine post__in and post__not_in in the same query, and you shouldn't need it anyways if your $upsells array already has the ids you want.

Would you mind sharing the whole template file you're working in, or at least more about how you're outputting the results of this query? It doesn't sound like the query you're working on is affecting your output at all, so that makes me think you have some kind of disconnect going on there.

Edit: It appears that you are using a very old version of woocommerce @ v1.6.4 that means your Woocommerce hasn't been updated since 2012! The function get_upsells() that you said you added is actually a woocommerce function, and it's deprecated in Woocommerce 3, which is what I'm testing on. I'd strongly suggest doing some updates before you continue attempting to fix anything.

I can't continue to help you until we know your issues aren't being caused by your out of date version of Woocommerce, and possibly an outdated version of WordPress.

About

Geeks Mental is a community that publishes articles and tutorials about Web, Android, Data Science, new techniques and Linux security.