Question 182469 in Woocommerce Question posted in
The official Woocommerce documentation can be found here.

Using GROUP BY on Woocommerce archive & category page

I want to GROUP BY my Woocommerce products on archive & category pages according to the custom field value called group_id. If there are products with the same group_id value, I want to list only one of them. How can I do it?

I tried this but it doesn’t work.

function custom_grouped_products_query( $q ) {
    if ( is_product_category() ) {
        global $wpdb;
        
        $q->query_vars['meta_query'][] = array(
            'relation' => 'OR',
            array(
                'key' => 'group_id',
                'compare' => 'NOT EXISTS',
            ),
            array(
                'key' => 'group_id',
                'compare' => 'EXISTS',
            ),
        );
        
        $q->set( 'orderby', 'meta_value' );
        $q->set( 'order', 'ASC' );
        $q->set( 'meta_key', 'group_id' );
        $q->set( 'posts_per_page', -1 );
        $q->set( 'post_type', 'product' );
        $q->set( 'suppress_filters', false );
        
        add_filter( 'posts_clauses', 'custom_grouped_products_clauses', 10, 2 );
    }
}
add_action( 'woocommerce_product_query', 'custom_grouped_products_query' );

function custom_grouped_products_clauses( $clauses, $q ) {
    global $wpdb;
    
    $clauses['groupby'] = "{$wpdb->postmeta}.meta_value";
    
    return $clauses;
}
Tags:

2

Answers


  1. 0

    Its not tested but maybe something like this ?

    function custom_grouped_products_clauses( $clauses, $q ) {
    global $wpdb;
    
    $clauses['groupby'] = "{$wpdb->postmeta}.meta_value";
    $clauses['orderby'] = "{$wpdb->postmeta}.meta_value ASC";
    //The join clause is modified to include a left join with the postmeta table, which allows us to access the meta_value column.
    $clauses['join'] .= " LEFT JOIN {$wpdb->postmeta} AS meta ON {$wpdb->posts}.ID = meta.post_id";
    //The where clause is modified to filter the results and include only products that have a matching group_id value. The subquery inside the where clause selects the post_id from the postmeta table where meta_key is 'group_id' and groups the results by meta_value.
    $clauses['where'] .= " AND (meta.meta_key = 'group_id' AND {$wpdb->posts}.ID IN (SELECT post_id FROM {$wpdb->postmeta} WHERE meta_key = 'group_id' GROUP BY meta_value))";
    
    return $clauses;
}
    Login or Signup to reply.
  2. 0 
    function custom_grouped_products_query( $q ) {
    if ( is_product_category() && ! is_admin() && $q->is_main_query() ) {
        $q->set( 'orderby', 'meta_value' );
        $q->set( 'order', 'ASC' );
        $q->set( 'meta_key', 'group_id' );
        $q->set( 'posts_per_page', -1 );
        $q->set( 'suppress_filters', false );
        
        add_filter( 'posts_groupby', 'custom_grouped_products_groupby', 10, 2 );
    }
}
add_action( 'pre_get_posts', 'custom_grouped_products_query' );

function custom_grouped_products_groupby( $groupby, $q ) {
    global $wpdb;
    
    $groupby .= " {$wpdb->postmeta}.meta_value";
    
    return $groupby;
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search