On a Wordpress page, I want to show all product categories followed by the total value of the products in that category, followed by a row with totals. For example:

So basically, I need to make a loop for every category and then add up every regular_price of those products. Finally, I need to addup all those prices to get the total.

Pseudo code / (not working):

$categories = get_categories();
foreach($categories as $category) {
   echo <a href="' . get_category_link($category->term_id) . '">' . $category->name . '</a>>';
}
$totals_per_category = array();
 foreach($categories as $key => $category) {
 foreach($category as $id => $value) {
                $totals_per_category[$id]  = $value;
            }
}
Echo $value;

Thanks for any advice how to make this work.

CodePudding user response：

To get all categories, you can use the get_terms function like this:

    $categories = get_terms(
        array(
            'taxonomy'   => 'product_cat',
            'orderby'    => 'name',
            'hide_empty' => false,
        )
    );

Feel free to modify the arguements as per your needs. Once you have an array of terms, you can use it to get the term id of each category and use the latter to run an SQL query that will allow you to get the total value of all products in that category. It can be done like this:

    $totals_per_category = array();
    foreach ($categories as $key => $category) {
        $term_id = $category->term_id;
        global $wpdb;
        $total = $wpdb->get_var(
            "
            SELECT sum(meta_value)
            FROM $wpdb->postmeta
            INNER JOIN {$wpdb->term_relationships} ON ( {$wpdb->term_relationships}.object_id = {$wpdb->postmeta}.post_id )
            WHERE ( {$wpdb->term_relationships}.term_taxonomy_id IN ($term_id) )
            AND {$wpdb->postmeta}.meta_key = '_regular_price'"
        );
        $totals_per_category[$term_id] = $total;
    }

Now since we have the total products value of each category, we can get the total of all categories using array_sum.

echo array_sum($totals_per_category); // This will output the total of all categories

Now you can use the logic above to retrieve totals and build the markup around it as you like (table, div ...etc).