How to get product details in loop with product group ID

[webtechwhiz]

Hi,

I am using latest Twenty-One theme of WHMCS. In landing page has following code:

{foreach $productGroups as $productGroup}
            <div class="mb-5">
                <div class=" text-center">
                    <h3 class="card-title pricing-card-title">
                        {$productGroup->name}
                    </h3>
                    <p>{$productGroup->tagline}</p>
                    <a href="{$productGroup->getRoutePath()}" class="btn btn-outline-primary my-3">
                        {lang key='browseProducts'}
                    </a>
                </div>
            </div>

        {/foreach}

Which is listing all Product  Groups.  I want to display all products under each product groups. For that I got below code through Support.

function homepage_products_hook($vars) {
//    print_r($vars);

//   $client = Menu::context('client');

   $products = Capsule::table('tblproducts')
                       ->join('tblpricing', 'tblproducts.id', '=', 'tblpricing.relid')
                       ->select('tblproducts.*','tblpricing.*')
                       ->where('tblpricing.type', 'product')
                       ->where('tblproducts.hidden','0')
                       ->where('tblproducts.gid','x')
                       ->groupBy('tblproducts.order')
                       ->get();

   $encodedata = json_encode($products);
   $decodedata = json_decode($encodedata, true);

   return array("myproducts" => $decodedata);
}
add_hook("ClientAreaPageHome", 1, "homepage_products_hook");

Which is returning null array. But when  I change x to 1 or 2 in ->where('tblproducts.gid','x') I get products of product group id 1 or 2 statically. I would like to know how I can get respective product group Product Details in loop. What value to be passed in place of x in ->where('tblproducts.gid','x') so that product output remains dynamic.

Will appreciate a working solution. Thanks

Regards,

Shubhajeet Saha

[webtechwhiz]

Can anyone provide me any solution to my requirement?

[brian!]

On 08/04/2021 at 11:30, webtechwhiz said:

Which is returning null array.

it will do - it's expecting a value to be entered for x. 🙂

On 08/04/2021 at 11:30, webtechwhiz said:

I would like to know how I can get respective product group Product Details in loop. What value to be passed in place of x in ->where('tblproducts.gid','x') so that product output remains dynamic.

you would loop through the groups array and use each id value to query for the products etc...

if you're going to do it based on my hook above and querying the db with capsule...

<?php

use WHMCS\Database\Capsule;

function show_products_on_homepage_hook($vars){
	
	$productgroups = $vars['productGroups'];
	foreach ($productgroups as $productGroup) {
		$myproductgroup = Group::find($productGroup->id);
		$products = Capsule::table('tblproducts')
			->join('tblpricing', 'tblproducts.id', '=', 'tblpricing.relid')
			->select('tblproducts.*','tblpricing.*')
			->where('tblpricing.type', 'product')
			->where('tblproducts.hidden','0')
			->where('tblproducts.gid',$productGroup->id)
			->groupBy('tblproducts.order')
			->get()
			->all();
		$myproducts[$productGroup->name]['products'] = $products;
	}
	return array("myproducts" => $myproducts);
}
add_hook("ClientAreaPageHome", 1, "show_products_on_homepage_hook");

that will give you an array that would need to be double looped through (e.g two nested foreach loops) - however, this could return a huge array depending on how many groups and products there are... I know in your case, there only seems to be 17 products, but I would still recommend tweaking the select line to only return the information that you require and not everything from the two tables.