Question 268381 in Wordpress Question posted in
The official Wordpress documentation can be found here.

WordPress – Woocommerce 8.x.x – Hook – woocommerce_product_related_posts_query

I’m trying to customise related product of woocommerce

First point is : the query is not updated for every requested page as I can imagine in term of server load.

So how could i force to stock the custom result for each page ?

Second point is : my request is to force related product to pick only product in the same main category.

But it doesn’t seem to work.

If you have any clue for me to find out why it doesn’t return the good product.

the final $query look like this

array(4) { ["fields"]=> string(45) " SELECT DISTINCT ID FROM WCCP_posts p " ["join"]=> string(548) " LEFT JOIN ( SELECT object_id FROM WCCP_term_relationships WHERE term_taxonomy_id IN ( 7 ) ) AS exclude_join ON exclude_join.object_id = p.ID INNER JOIN ( SELECT object_id FROM WCCP_term_relationships INNER JOIN WCCP_term_taxonomy using( term_taxonomy_id ) WHERE term_id IN ( 87,2490,2788,283,67 ) ) AS include_join ON include_join.object_id = p.ID INNER JOIN WCCP_term_relationships ON p.ID = WCCP_term_relationships.object_id INNER JOIN WCCP_term_taxonomy USING( term_taxonomy_id ) LEFT JOIN WCCP_icl_translations AS icl ON icl.element_id = p.ID " ["where"]=> string(263) " WHERE 1=1 AND p.post_status = 'publish' AND p.post_type = 'product' AND exclude_join.object_id IS NULL AND p.ID NOT IN ( 0,1705 ) AND WCCP_term_taxonomy.taxonomy = 'product_cat' AND WCCP_term_taxonomy.term_id = 2490 AND icl.language_code = 'fr' " ["limits"]=> string(17) " LIMIT 24 " }


if ( in_array( 'woocommerce/woocommerce.php', apply_filters( 'active_plugins', get_option( 'active_plugins' ) ) ) ) {

    function custom_related_products_query( $query, $product_id ) {
        global $wpdb;

        $categories = get_the_terms( $product_id, 'product_cat' );
        if ( empty( $categories ) ) {
            return $query;
        }
        var_dump($categories);
        // Get the main product category
        $main_category = null;
        foreach ( $categories as $category ) {
            if ( $category->parent == 0 ) {
                $main_category = $category;
                break;  
            }
        }

        // 
        if ( ! $main_category ) {
            echo "pas de categorie principale";
            return $query;
        }

        $query['join']   .= " INNER JOIN {$wpdb->term_relationships} ON p.ID = {$wpdb->term_relationships}.object_id";
        $query['join']   .= " INNER JOIN {$wpdb->term_taxonomy} USING( term_taxonomy_id )";
        $query['where']  .= " AND {$wpdb->term_taxonomy}.taxonomy = 'product_cat' AND {$wpdb->term_taxonomy}.term_id = {$main_category->term_id}";

        return $query;
    }
    add_filter( 'woocommerce_product_related_posts_query', 'custom_related_products_query', 10, 2 );

    function custom_related_products_orderby( $orderby ) {
        return "p.ID DESC";
    }
    add_filter( 'woocommerce_product_related_posts_orderby', 'custom_related_products_orderby', 10, 1 );
}

I tried the hook in function.php that i share on top of the post.

It should return a list of product id only linked to the same category of the product page

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I tried an other way by filtering every product that is not in same category, but it's working too much, sometimes the result si "no products" even there is product in this category

    add_filter( 'woocommerce_related_products', 'exclude_related_products', 10, 3 );

    function exclude_related_products( $related_posts, $product_id, $args ){ global $wpdb;

    $categories = get_the_terms( $product_id, 'product_cat' );
if ( empty( $categories ) ) {
    return $related_posts;
}


$cat_ids = wp_list_pluck( $categories, 'term_id' );


$exclude_ids = $wpdb->get_col(
        "SELECT DISTINCT p.ID
         FROM {$wpdb->posts} p
         INNER JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
         INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
         WHERE tt.taxonomy = 'product_cat'
         AND tt.term_id NOT IN (" . implode( ',', $cat_ids ) . ")
         AND p.post_status = 'publish'
         AND p.post_type = 'product'", 
);

// Excluez ces IDs de la liste des produits associés
return array_diff( $related_posts, $exclude_ids );

    }


  2. 0

    Ok just to share with you

    I finally try an other way and success on this request :

    So this added to function.php with Yoast plugin activated will :

    Show only the last 4 products of the current product’s category

    add_filter('woocommerce_related_products', 'include_related_products',10 , 3 );

function include_related_products($related_posts, $product_id, $args){
    global $wpdb;
    $primary_category_id = null;
    $primary_category_id = yoast_get_primary_term_id('product_cat');
    if ( ! $primary_category_id ) {
        return $related_posts;
    }
    $include_ids = $wpdb->get_col(
            "SELECT DISTINCT p.ID
             FROM {$wpdb->posts} p
             INNER JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
             INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
             WHERE tt.taxonomy = 'product_cat'
             AND tt.term_id = " . $primary_category_id . "
             AND p.post_status = 'publish'
             AND p.post_type = 'product'
             ORDER BY p.ID DESC
             LIMIT 4" 
    );
    return array_diff($include_ids);
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search