[SOLVED] Exclude category(s) from Special page.

Quote

Post by kec » Thu May 04, 2023 3:09 am

Hi,

I thought there were lots of mods for this "simple" question, but after hours of searching it looks like there's not a single (paid/free) mod that can handle this apparently difficult change.

So my question for a customer of us with standard OC 3.0.3.7 is: How can we exclude a category from the SPECIAL page, in which normally ALL products with Special prices are shown?

Hope for an answer.

Kind regards,

Kec
Last edited by kec on Tue Jan 07, 2025 4:50 pm, edited 1 time in total.
kec
New member
Posts
98
Joined
Sat Apr 20, 2013 6:06 pm
Top

Re: Exclude category from Special page?

Quote

Post by straightlight » Thu May 04, 2023 4:34 am

kec wrote: ↑
Thu May 04, 2023 3:09 am
 Hi,

I thought there were lots of mods for this "simple" question, but after hours of searching it looks like there's not a single (paid/free) mod that can handle this apparently difficult change.

So my question for a customer of us with standard OC 3.0.3.7 is: How can we exclude a category from the SPECIAL page, in which normally ALL products with Special prices are shown?

Hope for an answer.

Kind regards,

Kec
The quickest way would be by filtering your own category IDs in your catalog/controller/product/special.php and your catalog/controller/extension/module/special.php file. In OC v.3.0.3.8+, a new method has been added in order to validate a product ID with an array of category IDs.

In both controller files, you could then lookup for your product ID that are all related with the category IDs you may not want to include by using the:

Code: Select all

checkProductCategories()
method. More information can be found here: https://github.com/opencart/opencart-3/ ... t.php#L544 .

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester

straightlight
Legendary Member
Posts
22525
Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON
Top

Re: Exclude category from Special page?

Quote

Post by straightlight » Thu May 04, 2023 10:17 am

The following changes are untested. It now includes the filter_categories and filter_filter array keys with the filter_data when using the product specials. From the controllers' locations specified above, you can use your category IDs at your discretion and also with an extension if the $parts needs to be used with the path_id or the category_id in the meantime.

Therefore, the following modifications are based on the opencart-3 repository and PHP 8.2+ .

In catalog/model/catalog/product.php file,

replace the entire getSpecials method:

Code: Select all

public function getSpecials(array $data = []): array {
        $sql = "SELECT DISTINCT ps.`product_id`, (SELECT AVG(`rating`) FROM `" . DB_PREFIX . "review` r1 WHERE r1.`product_id` = ps.`product_id` AND r1.`status` = '1' GROUP BY r1.`product_id`) AS rating FROM `" . DB_PREFIX . "product_special` ps LEFT JOIN `" . DB_PREFIX . "product` p ON (ps.`product_id` = p.`product_id`) LEFT JOIN `" . DB_PREFIX . "product_description` pd ON (p.`product_id` = pd.`product_id`) LEFT JOIN `" . DB_PREFIX . "product_to_store` p2s ON (p.`product_id` = p2s.`product_id`) WHERE p.`status` = '1' AND p.`date_available` <= NOW() AND p2s.`store_id` = '" . (int)$this->config->get('config_store_id') . "' AND ps.`customer_group_id` = '" . (int)$this->config->get('config_customer_group_id') . "' AND ((ps.`date_start` = '0000-00-00' OR ps.`date_start` < NOW()) AND (ps.`date_end` = '0000-00-00' OR ps.`date_end` > NOW())) GROUP BY ps.`product_id`";

        $sort_data = [
            'pd.name',
            'p.model',
            'ps.price',
            'rating',
            'p.sort_order'
        ];

        if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
            if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
                $sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
            } else {
                $sql .= " ORDER BY " . $data['sort'];
            }
        } else {
            $sql .= " ORDER BY p.`sort_order`";
        }

        if (isset($data['order']) && ($data['order'] == 'DESC')) {
            $sql .= " DESC, LCASE(pd.`name`) DESC";
        } else {
            $sql .= " ASC, LCASE(pd.`name`) ASC";
        }

        if (isset($data['start']) || isset($data['limit'])) {
            if ($data['start'] < 0) {
                $data['start'] = 0;
            }

            if ($data['limit'] < 1) {
                $data['limit'] = 20;
            }

            $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
        }

        $product_data = [];

        $query = $this->db->query($sql);

        foreach ($query->rows as $result) {
            $product_data[$result['product_id']] = $this->model_catalog_product->getProduct($result['product_id']);
        }

        return $product_data;
    }
with:

Code: Select all

public function getSpecials(array $data = []): array {
        $sql = "SELECT DISTINCT ps.`product_id`, (SELECT AVG(`rating`) FROM `" . DB_PREFIX . "review` r1 WHERE r1.`product_id` = ps.`product_id` AND r1.`status` = '1' GROUP BY r1.`product_id`) AS rating FROM `" . DB_PREFIX . "product_special` ps LEFT JOIN `" . DB_PREFIX . "product_to_store` p2s ON (ps.`product_id` = p2s.`product_id`)";
		
		if (!empty($data['filter_categories'])) {
            if (!empty($data['filter_sub_category'])) {
                $sql .= " LEFT JOIN `" . DB_PREFIX . "category_path` cp LEFT JOIN `" . DB_PREFIX . "product_to_category` p2c ON (cp.`category_id` = p2c.`category_id`)";
            } else {
                $sql .= " LEFT JOIN `" . DB_PREFIX . "product_to_category` p2c";
            }

            if (!empty($data['filter_filter'])) {
                $sql .= " LEFT JOIN `" . DB_PREFIX . "product_filter` pf ON (p2c.`product_id` = pf.`product_id`) LEFT JOIN `" . DB_PREFIX . "product` p ON (pf.`product_id` = p.`product_id`)";
            } else {
                $sql .= " LEFT JOIN `" . DB_PREFIX . "product` p ON (p2c.`product_id` = p.`product_id`)";
            }
        } else {
            $sql .= " LEFT JOIN `" . DB_PREFIX . "product` p ON (ps.`product_id` = p.`product_id`)";
        }

        $sql .= " LEFT JOIN `" . DB_PREFIX . "product_description` pd ON (ps.`product_id` = pd.`product_id`)";
		
		$sql .= " WHERE pd.`language_id` = '" . (int)$this->config->get('config_language_id') . "' AND p.`date_available` <= NOW() AND p2s.`store_id` = '" . (int)$this->config->get('config_store_id') . "' AND p.`status` = '1' AND p.`date_available` <= NOW() AND p2s.`store_id` = '" . (int)$this->config->get('config_store_id') . "' AND ps.`customer_group_id` = '" . (int)$this->config->get('config_customer_group_id') . "' AND ((ps.`date_start` = '0000-00-00' OR ps.`date_start` < NOW()) AND (ps.`date_end` = '0000-00-00' OR ps.`date_end` > NOW()))";

        if (!empty($data['filter_categories'])) {
			if (!empty($data['filter_sub_category'])) {							
				$implode = [];
				
				foreach ($data['filter_categories'] as $path_id) {
					$implode[] = "cp.`path_id` = '" . (int)$path_id. "'";
				}				
            } else {
				$implode = [];
				
				foreach ($data['filter_categories'] as $category_id) {				
					$implode[] = "p2c.`category_id` = '" . (int)$category_id. "'";
				}
            }
			
			if (isset($implode)) {
				$sql .= " AND (" . implode(" OR ", $implode) . ")";
			}

            if (!empty($data['filter_filter'])) {
                $implode = [];

                $filters = explode(',', $data['filter_filter']);

                foreach ($filters as $filter_id) {
                    $implode[] = (int)$filter_id;
                }

                $sql .= " AND pf.`filter_id` IN(" . implode(',', $implode) . ")";
            }
        }
		
		$sql .= " GROUP BY ps.`product_id`";

        $sort_data = [
            'pd.name',
            'p.model',
            'ps.price',
            'rating',
            'p.sort_order'
        ];

        if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
            if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
                $sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
            } else {
                $sql .= " ORDER BY " . $data['sort'];
            }
        } else {
            $sql .= " ORDER BY p.`sort_order`";
        }

        if (isset($data['order']) && ($data['order'] == 'DESC')) {
            $sql .= " DESC, LCASE(pd.`name`) DESC";
        } else {
            $sql .= " ASC, LCASE(pd.`name`) ASC";
        }

        if (isset($data['start']) || isset($data['limit'])) {
            if ($data['start'] < 0) {
                $data['start'] = 0;
            }

            if ($data['limit'] < 1) {
                $data['limit'] = 20;
            }

            $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
        }

        $product_data = [];

        $query = $this->db->query($sql);

        foreach ($query->rows as $result) {
            $product_data[$result['product_id']] = $this->model_catalog_product->getProduct($result['product_id']);
        }

        return $product_data;
    }
Then, replace the entire getTotalSpecials method:

Code: Select all

public function getTotalSpecials(): int {
        $query = $this->db->query("SELECT COUNT(DISTINCT ps.`product_id`) AS `total` FROM `" . DB_PREFIX . "product_special` ps LEFT JOIN `" . DB_PREFIX . "product` p ON (ps.`product_id` = p.`product_id`) LEFT JOIN `" . DB_PREFIX . "product_to_store` p2s ON (p.`product_id` = p2s.`product_id`) WHERE p.`status` = '1' AND p.`date_available` <= NOW() AND p2s.`store_id` = '" . (int)$this->config->get('config_store_id') . "' AND ps.`customer_group_id` = '" . (int)$this->config->get('config_customer_group_id') . "' AND ((ps.`date_start` = '0000-00-00' OR ps.`date_start` < NOW()) AND (ps.`date_end` = '0000-00-00' OR ps.`date_end` > NOW()))");

        if (isset($query->row['total'])) {
            return (int)$query->row['total'];
        } else {
            return 0;
        }
    }
with:

Code: Select all

public function getTotalSpecials(): int {
		$sql = "SELECT COUNT(DISTINCT ps.`product_id`) AS `total` FROM `" . DB_PREFIX . "product_special` ps LEFT JOIN `" . DB_PREFIX . "product_to_store` p2s ON (ps.`product_id` = p2s.`product_id`)";
		
		if (!empty($data['filter_categories'])) {
            if (!empty($data['filter_sub_category'])) {
                $sql .= " LEFT JOIN `" . DB_PREFIX . "category_path` cp LEFT JOIN `" . DB_PREFIX . "product_to_category` p2c ON (cp.`category_id` = p2c.`category_id`)";
            } else {
                $sql .= " LEFT JOIN `" . DB_PREFIX . "product_to_category` p2c";
            }

            if (!empty($data['filter_filter'])) {
                $sql .= " LEFT JOIN `" . DB_PREFIX . "product_filter` pf ON (p2c.`product_id` = pf.`product_id`) LEFT JOIN `" . DB_PREFIX . "product` p ON (pf.`product_id` = p.`product_id`)";
            } else {
                $sql .= " LEFT JOIN `" . DB_PREFIX . "product` p ON (p2c.`product_id` = p.`product_id`)";
            }
        } else {
            $sql .= " LEFT JOIN `" . DB_PREFIX . "product` p ON (ps.`product_id` = p.`product_id`)";
        }

        $sql .= " LEFT JOIN `" . DB_PREFIX . "product_description` pd ON (ps.`product_id` = pd.`product_id`)";
		
		$sql .= " WHERE pd.`language_id` = '" . (int)$this->config->get('config_language_id') . "' AND p.`date_available` <= NOW() AND p2s.`store_id` = '" . (int)$this->config->get('config_store_id') . "' AND p.`status` = '1' AND p.`date_available` <= NOW() AND p2s.`store_id` = '" . (int)$this->config->get('config_store_id') . "' AND ps.`customer_group_id` = '" . (int)$this->config->get('config_customer_group_id') . "' AND ((ps.`date_start` = '0000-00-00' OR ps.`date_start` < NOW()) AND (ps.`date_end` = '0000-00-00' OR ps.`date_end` > NOW()))";

        if (!empty($data['filter_categories'])) {
			if (!empty($data['filter_sub_category'])) {							
				$implode = [];
				
				foreach ($data['filter_categories'] as $path_id) {
					$implode[] = "cp.`path_id` = '" . (int)$path_id. "'";
				}				
            } else {
				$implode = [];
				
				foreach ($data['filter_categories'] as $category_id) {				
					$implode[] = "p2c.`category_id` = '" . (int)$category_id. "'";
				}
            }
			
			if (isset($implode)) {
				$sql .= " AND (" . implode(" OR ", $implode) . ")";
			}

            if (!empty($data['filter_filter'])) {
                $implode = [];

                $filters = explode(',', $data['filter_filter']);

                foreach ($filters as $filter_id) {
                    $implode[] = (int)$filter_id;
                }

                $sql .= " AND pf.`filter_id` IN(" . implode(',', $implode) . ")";
            }
        }
		
		$query = $this->db->query($sql);

		if (isset($query->row['total'])) {
			return (int)$query->row['total'];
		} else {
			return 0;
		}
	}
This should resolve the issue.
Last edited by straightlight on Fri May 05, 2023 12:32 am, edited 2 times in total.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester

straightlight
Legendary Member
Posts
22525
Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON
Top

Re: Exclude category from Special page?

Quote

Post by kec » Thu May 04, 2023 3:57 pm

Hi Straightlight,

First: thank you for your time to respond.

In your first answer you talk about the

Code: Select all

checkProductCategories()
Do i need to implement the ID of the category to exclude within the ()?

Hope for an answer,

Kind regards,

Kec
kec
New member
Posts
98
Joined
Sat Apr 20, 2013 6:06 pm
Top

Re: Exclude category from Special page?

Quote

Post by straightlight » Thu May 04, 2023 10:12 pm

kec wrote: ↑
Thu May 04, 2023 3:57 pm
 Hi Straightlight,

First: thank you for your time to respond.

In your first answer you talk about the

Code: Select all

checkProductCategories()
Do i need to implement the ID of the category to exclude within the ()?

Hope for an answer,

Kind regards,

Kec
Using the checkProductCategories() method requires the product ID and the category IDs array as described on the link I provided above.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester

straightlight
Legendary Member
Posts
22525
Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON
Top

Re: Exclude category from Special page?

Quote

Post by kec » Thu May 04, 2023 11:12 pm

Thank you again for your time and effort.

Unfortunately I'm not a programmer. The only thing I have is the category ID of the discount page (102) and the category ID of the page (68) which must be excluded from the specials.
kec
New member
Posts
98
Joined
Sat Apr 20, 2013 6:06 pm
Top

Re: Exclude category from Special page?

Quote

Post by straightlight » Fri May 05, 2023 12:19 am

kec wrote: ↑
Thu May 04, 2023 11:12 pm
 Thank you again for your time and effort.

Unfortunately I'm not a programmer. The only thing I have is the category ID of the discount page (102) and the category ID of the page (68) which must be excluded from the specials.
You can always create a new service request in the Commercial Support section of the forum or contact me directly via the forum PM to get this done as a custom job.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester

straightlight
Legendary Member
Posts
22525
Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON
Top

Re: Exclude category from Special page?

Quote

Post by kec » Mon Jan 06, 2025 11:46 pm

Hi there,

Back again with the same question. Unfortunately the previous solution isn't working any more, because of an update from a mod, so my question is relevant again: How can I exclude a Category from the Special-page? Any help would be highly appreciated.
kec
New member
Posts
98
Joined
Sat Apr 20, 2013 6:06 pm
Top

Re: Exclude category from Special page?

Quote

Post by nonnedelectari » Tue Jan 07, 2025 11:49 am

kec wrote: ↑
Mon Jan 06, 2025 11:46 pm
 Hi there,

Back again with the same question. Unfortunately the previous solution isn't working any more, because of an update from a mod, so my question is relevant again: How can I exclude a Category from the Special-page? Any help would be highly appreciated.
If you mean excluding specials assigned to specific categories from the specials page.

Well, without fancy background admin control, you could add this to you config.php files:

Code: Select all

// category ids of categories to be excluded from specials page, use your own ids
define('SPECIALS_EXCLUDE_CATEGORIES',array('78','34'));
then extend the specials query in both getTotalProductSpecials and getProductSpecials methods of catalog/model/catalog/product.php with:

Code: Select all

AND ps.product_id NOT IN 
(SELECT product_id FROM " . DB_PREFIX . "product_to_category WHERE category_id IN (".implode(',',SPECIALS_EXCLUDE_CATEGORIES)."))
That will basically filter out any product (specials in this case) which are assigned to any of the categories you defined in SPECIALS_EXCLUDE_CATEGORIES.
nonnedelectari
Active Member
Posts
243
Joined
Thu Mar 04, 2021 6:34 pm
Top

Re: Exclude category from Special page?

Quote

Post by kec » Tue Jan 07, 2025 4:49 pm

YES!! Thank you! :D :D

Your coding did the job!
kec
New member
Posts
98
Joined
Sat Apr 20, 2013 6:06 pm
Top
Post Reply
Return to “Extensions Support”
Who is online

Users browsing this forum: No registered users and 15 guests