1

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 like&hellip;', 
'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!

  • I've also tried using 'orderby' => 'post__in' which In theory should work, but again nothing changes :( – Liam Winterton May 17 '17 at 9:36
  • Try changing 'order' => 'asc', to 'order' => 'desc', – xvilo May 17 '17 at 9:38
  • @xvilo, unfortunately, this doesn't even reverse the incorrect order that's currently being shown. (Tried all variants of 'orderby' => 'menu_order / post__in' and 'order' => 'asc / desc') – Liam Winterton May 17 '17 at 9:42
  • if you change back to 'orderby'=>'menu_order' then I would think it should order them by page attributes set on back end. Not sure if the page attributes meta box displays on your product editing screen though? – Ben HartLenn May 17 '17 at 9:43
  • @BenHartLenn I'm just using the standard WooCommerce product backend, linking the products from that product directly. Screenshot – Liam Winterton May 17 '17 at 9:52
0

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.

  • Yeah course, give me just a sec. – Liam Winterton May 17 '17 at 12:34
  • Updated my original question with the file in question. – Liam Winterton May 17 '17 at 12:46
  • @LiamWinterton Well I think there's something else going on here now. I can just paste all of your code into my up-sells.php file, and all I do is change your $uploads variable to be a static array of product ids, and it all works fine. Maybe try to add some random text to your up-sells.php file and check if your text loads in the front end. This ensures the template file you're editing is being loaded properly. If that is working, then maybe you're experiencing an aggressive caching issue of some kind? – Ben HartLenn May 17 '17 at 19:59
  • If I change a few things around, I do get changes on the page, I can print_r(); things so this file is definitely in use. We've had issues with the site caching things in the past, but this is run locally with no caching plugins used. – Liam Winterton May 18 '17 at 8:25
  • I've just updated the question with the SQL query that gets ran. It seems that orderby => post__in isn't respected and defaults to menu_order – Liam Winterton May 18 '17 at 9:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.