Page 1 of 2
Product Listing under Category List
Posted: Sun Apr 16, 2023 6:10 pm
by tessthepup
Hi,
I have copied and changed the product_listing module to be an index_nested module so I can display products under sub categories.
What I would like to happen is it to only show products from the sub categories listed under the main category.
Could anyone suggest a change to this code to only show products from the sub categories
Code: Select all
$listing_sql = sprintf(<<<'EOSQL'
SELECT m.*, %s
FROM
products p
LEFT JOIN specials s ON p.products_id = s.products_id
INNER JOIN products_description pd ON p.products_id = pd.products_id
LEFT JOIN (SELECT products_id, COUNT(*) AS attribute_count FROM products_attributes GROUP BY products_id) a ON p.products_id = a.products_id
EOSQL
, Product::COLUMNS);
// show the products of a specified manufacturer
if (empty($_GET['manufacturers_id'])) {
// show the products in a given category
if (isset($_GET['filter_id']) && !Text::is_empty($_GET['filter_id'])) {
// We are asked to show only a specific manufacturer
$listing_sql .= sprintf(<<<'EOSQL'
INNER JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id
WHERE p.products_status = 1 AND m.manufacturers_id = %d AND pd.language_id = %d AND p2c.categories_id = %d
EOSQL
, (int)$_GET['filter_id'], (int)$_SESSION['languages_id'], (int)$current_category_id);
} else {
// We show them all
$listing_sql .= sprintf(<<<'EOSQL'
INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
WHERE p.products_status = 1 AND pd.language_id = %d AND p2c.categories_id = %d
EOSQL
, (int)$_SESSION['languages_id'], (int)$current_category_id);
}
} else {
if (isset($_GET['filter_id']) && !Text::is_empty($_GET['filter_id'])) {
// We are asked to show only a specific category
$listing_sql .= sprintf(<<<'EOSQL'
INNER JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id
WHERE p.products_status = 1 AND m.manufacturers_id = %d AND pd.language_id = %d AND p2c.categories_id = %d
EOSQL
, (int)$_GET['manufacturers_id'], (int)$_SESSION['languages_id'], (int)$_GET['filter_id']);
} else {
// We show them all
$listing_sql .= sprintf(<<<'EOSQL'
INNER JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
WHERE p.products_status = 1 AND pd.language_id = %d AND m.manufacturers_id = %d
EOSQL
, (int)$_SESSION['languages_id'], (int)$_GET['manufacturers_id']);
}
}
Thanks
Re: Product Listing under Category List
Posted: Sun Apr 16, 2023 7:58 pm
by Kofod95
Maybe change p2c.categories_id = %d to p2c.categories_id IN (" . Tree->get_descendants((int)$current_category_id) . ")
//Daniel
Re: Product Listing under Category List
Posted: Sun Apr 16, 2023 8:28 pm
by tessthepup
Kofod95 wrote: ↑Sun Apr 16, 2023 7:58 pm
Maybe change p2c.categories_id = %d to p2c.categories_id IN (" . Tree->get_descendants((int)$current_category_id) . ")
//Daniel
Thanks Daniel but that did not work as no products were shown.
Re: Product Listing under Category List
Posted: Sun Apr 16, 2023 9:50 pm
by Kofod95
Did you change the first instance or the second or both?
//Daniel
Re: Product Listing under Category List
Posted: Sun Apr 16, 2023 9:51 pm
by ecartz
and
Code: Select all
implode(', ', array_map('intval', Guarantor::ensure_global('category_tree')->get_descendants((int)$current_category_id)))
replaces
Re: Product Listing under Category List
Posted: Sun Apr 16, 2023 9:53 pm
by Kofod95
I was close enough to make me happy
//Daniel
Re: Product Listing under Category List
Posted: Mon Apr 17, 2023 4:57 pm
by tessthepup
@ecartz @Kofod95
Thanks guys, although this error happens
PHP Warning: DB: [1064] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(" . Tree->get_descendants(implode(', ', array_map('intval', Guarantor::ensur...' at line 7 from <SELECT COUNT(p.products_id) AS total FROM
products p
LEFT JOIN specials s ON p.products_id = s.products_id
INNER JOIN products_description pd ON p.products_id = pd.products_id
LEFT JOIN (SELECT products_id, COUNT(*) AS attribute_count FROM products_attributes GROUP BY products_id) a ON p.products_id = a.products_id INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
WHERE p.products_status = 1 AND pd.language_id = 1 AND p2c.categories_id IN (463) (" . Tree->get_descendants(implode(', ', array_map('intval', Guarantor::ensure_global('category_tree')->get_descendants((int)$current_category_id)))) . ") > in /home/***/public_html/includes/system/versioned/1.0.8.1/database_core.php on line 44
or did I just mess it up again lol
Code: Select all
// We show them all
$listing_sql .= sprintf(<<<'EOSQL'
INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
WHERE p.products_status = 1 AND pd.language_id = %d AND p2c.categories_id IN (%s) (" . Tree->get_descendants(implode(', ', array_map('intval', Guarantor::ensure_global('category_tree')->get_descendants((int)$current_category_id)))) . ")
EOSQL
, (int)$_SESSION['languages_id'], (int)$current_category_id);
Re: Product Listing under Category List
Posted: Mon Apr 17, 2023 8:43 pm
by Kofod95
tessthepup wrote: ↑Mon Apr 17, 2023 4:57 pm
Code: Select all
// We show them all
$listing_sql .= sprintf(<<<'EOSQL'
INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
WHERE p.products_status = 1 AND pd.language_id = %d AND p2c.categories_id IN (%s) (" . Tree->get_descendants(implode(', ', array_map('intval', Guarantor::ensure_global('category_tree')->get_descendants((int)$current_category_id)))) . ")
EOSQL
, (int)$_SESSION['languages_id'], (int)$current_category_id);
Should be
Code: Select all
// We show them all
$listing_sql .= sprintf(<<<'EOSQL'
INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
WHERE p.products_status = 1 AND pd.language_id = %d AND p2c.categories_id IN (%s)
EOSQL
, (int)$_SESSION['languages_id'], implode(', ', array_map('intval', Guarantor::ensure_global('category_tree')->get_descendants((int)$current_category_id))));
//Daniel
Re: Product Listing under Category List
Posted: Mon Apr 17, 2023 8:53 pm
by tessthepup
@Kofod95
Thanks Daniel, getting this fatal error now
PHP Fatal error: Uncaught Error: Undefined constant "Tree" in
I have searched github but cant seem to find any reference to Tree
Re: Product Listing under Category List
Posted: Mon Apr 17, 2023 8:56 pm
by Kofod95
Sorry, copy error on my part. Last post edited.
//Daniel