Change filtration method in Opencart 3 Category Bestseller module

I have a module for Opencart 3 – Category Bestseller. It shows the popular categories in which the most items are sold. I would like to change this filter. Make it so that it shows the categories in which the most viewed products. After sorting through the files, I found that this was written in the model.

Here is model file

<?php
class ModelExtensionModuleBestsellerCategory extends Model {

    public function getBestSellerCategories($limit) {
        $category_data = $this->cache->get('category.bestseller.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $this->config->get('config_customer_group_id') . '.' . (int)$limit);

        if (!$category_data) {
            $category_data = array();

            $query = $this->db->query("SELECT c.category_id, SUM(op.quantity) AS total  FROM " . DB_PREFIX . "order_product op LEFT JOIN `" . DB_PREFIX . "order` o ON (op.order_id = o.order_id) LEFT JOIN `" . DB_PREFIX . "product` p ON (op.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN `" . DB_PREFIX . "product_to_category` p2c ON (p2c.product_id = p.product_id) LEFT JOIN `" . DB_PREFIX . "category` c ON (p2c.category_id = c.category_id) WHERE o.order_status_id > '0' AND p.status = '1' AND c.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' GROUP BY p2c.category_id ORDER BY total DESC LIMIT " . (int)$limit);
            
            $category_data =  $query->rows;

            $this->cache->set('category.bestseller.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $this->config->get('config_customer_group_id') . '.' . (int)$limit, $category_data);
        }

        return $category_data;
    }
}

I think that sorting is here. What I need to change?

$query = $this->db->query("SELECT c.category_id, SUM(op.quantity) AS total  FROM " . DB_PREFIX . "order_product op LEFT JOIN `" . DB_PREFIX . "order` o ON (op.order_id = o.order_id) LEFT JOIN `" . DB_PREFIX . "product` p ON (op.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN `" . DB_PREFIX . "product_to_category` p2c ON (p2c.product_id = p.product_id) LEFT JOIN `" . DB_PREFIX . "category` c ON (p2c.category_id = c.category_id) WHERE o.order_status_id > '0' AND p.status = '1' AND c.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' GROUP BY p2c.category_id ORDER BY total DESC LIMIT " . (int)$limit);

Answer

Replace the query string with this. So the categories will be filtered by number of views using the viewed property from the {prefix}_products table.

$query = $this->db->query("SELECT c.category_id, SUM(p.viewed) AS total FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN `" . DB_PREFIX . "product_to_category` p2c ON (p2c.product_id = p.product_id) LEFT JOIN `" . DB_PREFIX . "category` c ON (p2c.category_id = c.category_id) WHERE  p.status = '1' AND c.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' GROUP BY p2c.category_id ORDER BY total DESC LIMIT " . (int)$limit);

After changing the model, you need to reset the modification cache.

Advertisement

Answer