I need show products from category with all subcategories.
I did it this way:
In file tables/category.php
FIND (about line 173):
$query = "SELECT prod.product_id, categ.category_id, prod.`".$lang->get('name')."` as name, prod.`".$lang->get('short_description')."` as short_description, prod.product_ean, prod.product_thumb_image, prod.product_price, prod.product_tax_id as tax_id, prod.product_old_price, prod.product_weight, prod.average_rating, prod.reviews_count, prod.hits, prod.weight_volume_units, prod.basic_price_unit_id, prod.label_id, prod.product_manufacturer_id, prod.product_weight, prod.min_price, prod.product_quantity, prod.different_prices $adv_result
FROM `#__jshopping_products` AS prod
LEFT JOIN `#__jshopping_products_to_categories` AS categ USING (product_id)
$adv_from
WHERE categ.category_id = '".$this->_db->getEscaped($this->category_id)."' AND prod.product_publish = '1' ".$adv_query;
REPLACE TO:
$categories = $this->getAllSubcatogories($this->category_id);
$query = "SELECT prod.product_id, max(categ.category_id) as category_id, prod.`".$lang->get('name')."` as name, prod.`".$lang->get('short_description')."` as short_description, prod.product_ean, prod.product_thumb_image, prod.product_price, prod.product_tax_id as tax_id, prod.product_old_price, prod.product_weight, prod.average_rating, prod.reviews_count, prod.hits, prod.weight_volume_units, prod.basic_price_unit_id, prod.label_id, prod.product_manufacturer_id, prod.product_weight, prod.min_price, prod.product_quantity, prod.different_prices $adv_result
FROM `#__jshopping_products` AS prod
LEFT JOIN `#__jshopping_products_to_categories` AS categ USING (product_id)
$adv_from
WHERE categ.category_id IN ($categories) AND prod.product_publish = '1' GROUP BY prod.product_id ".$adv_query;
FIND (about line 222):
$query = "SELECT count(pr_cat.product_id) FROM `#__jshopping_products_to_categories` AS pr_cat
INNER JOIN `#__jshopping_products` AS prod ON pr_cat.product_id = prod.product_id
WHERE pr_cat.category_id = '".$this->_db->getEscaped($this->category_id)."' ".$adv_query;
REPLACE TO:
$categories = $this->getAllSubcatogories($this->category_id);
$query = "SELECT count(DISTINCT prod.product_id) FROM `#__jshopping_products_to_categories` AS pr_cat
LEFT JOIN `#__jshopping_products` AS prod ON pr_cat.product_id = prod.product_id
WHERE pr_cat.category_id IN ($categories) ".$adv_query;
BEFORE
}
?>
ADD
/*
* Get category id with all subcategories ids
*/
function getAllSubcatogories($id) {
$categories = array();
$newCategories = array($id);
do {
$categories = array_merge($categories, $newCategories);
$categoriesStr = implode(',', $newCategories);
$query = "SELECT category_id FROM #__jshopping_categories WHERE category_parent_id IN ($categoriesStr)";
$this->_db->setQuery($query);
$newCategories = $this->_db->loadResultArray();
} while (!empty($newCategories));
return implode(',', $categories);
}
In file controller/category.php
FIND
addLinkToProducts($products, $category_id);
REPLACE TO
addLinkToProducts($products);
for links to categories of products.
Can you add showing of products from subcategories in future versions?
Joomla: 1.7
JoomShopping: 3.2.6
JoomShopping: 3.2.6