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

Group by wordpress posts by meta value – Woocommerce

I have about 2000 woocommerce products on my wordpress database, each product has an ACF select field named place which value is local or regional.

My goal is to group by each products by the place meta field so i I thought that following code do the job :

class Search
{
  public function __construct() 
  {
      add_action("pre_get_posts", [$this, "filterProducts"]);
      add_filter('posts_groupby', [$this, 'groupByFilter'] );
  }
  public function filterProducts($query){
    // ... code
    // This where i alter que query 
   // .. code
   return $query;
  }
  public function groupByFilter($groupby)
  {
     global $wpdb;
     return $wpdb->postmeta . ".meta_key = 'place'";
  }
}

But, this not working, and output only one product while the expected result is :

— LOCAL

  • product
  • product

— REGIONAL

  • product
  • product

I know foreach over each product is a solution, but for performance reasons i am looking do to it directly throught main query.

NOTE : if it helps, I use timber and in the search.php file I have this piece of code :

$context = Timber::get_context();
$context["posts"] = new TimberPostQuery();
// print the results
dump($context['posts']);

UPDATE :
The sql results output this query :

SELECT 6288gjvs_posts.* FROM 6288gjvs_posts 
WHERE 1=1  
AND 6288gjvs_posts.post_type = 'product'  
AND (6288gjvs_posts.post_status = 'publish' 
OR 6288gjvs_posts.post_status = 'acf-disabled' 
OR 6288gjvs_posts.post_status = 'complete' 
OR 6288gjvs_posts.post_status = 'paid' 
OR 6288gjvs_posts.post_status = 'confirmed' 
OR 6288gjvs_posts.post_status = 'unpaid' 
OR 6288gjvs_posts.post_status = 'pending-confirmation' 
OR 6288gjvs_posts.post_status = 'cancelled' 
OR 6288gjvs_posts.post_status = 'private') 
GROUP BY 6288gjvs_postmeta.meta_key = 'place' 
ORDER BY 6288gjvs_posts.menu_order, RAND(1348526234)

Copied it and pasted on phpmyadmin with error output :
#1054 – Champ ‘6288gjvs_postmeta.meta_key’ not known in group statement

So i left join the post meta :

SELECT 6288gjvs_posts.post_title, 6288gjvs_posts.ID, 6288gjvs_postmeta.post_id  
FROM 6288gjvs_posts  
LEFT JOIN 6288gjvs_postmeta 
ON 6288gjvs_posts.ID = 6288gjvs_postmeta.post_id 
AND 6288gjvs_posts.post_type = 'product'  
GROUP BY 6288gjvs_postmeta.meta_key = 'place'

Without success

UPDATE 2

Hello, so finally i opted with two queries, i didn’t found any solution, now I have to manage two paginations in one page :(. If someone find a solution, i always follow up this post and may be useful for others.

Thank you for your helps

Tags:

2

Answers


  1. 0

    If you want to filter your posts by a meta key, you should definitally take a look at WP_Meta_Query.

    So like you did with ‘tax_query’ in the ‘pre_get_posts’ filter, you should add another query in the main query like this:

    $meta_query[] = array(
                'key'     => 'place',
                'value'   => 'your_value',
                'compare' => '=',
        );
$query->set('meta_query', $meta_query);

    I hope you find this usefull and it solves your problem.

    Login or Signup to reply.
  2. 0

    It is a lot easier and advisable to get posts using WP_Query instead of modifying the SQL codes that may affect other post types and queries. In your scenario where you are getting WooCommerce products, use WC_Product_Query. Below is an example that will get you products with meta_key = place

    function get_xx_products()
{
    $args = array(
        'my_custom_var'         => array(
            array(
                'key'           =>  'place',
                'value'         =>  '',
                'meta_compare'  =>  'EXISTS'
            )
        ),
        'status'                => 'publish'
    );
    return wc_get_products($args);
}

function hook_my_custom_var($query, $query_vars)
{
    if (!empty($query_vars['my_custom_var'])) {
        $query['meta_query'] = $query_vars['my_custom_var'];
    }
    
    return $query;
}
add_filter('woocommerce_product_data_store_cpt_get_products_query', 'hook_my_custom_var', 10, 2);

    You can read more about it here – https://github.com/woocommerce/woocommerce/wiki/wc_get_products-and-WC_Product_Query

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search