Bestseller products selected according to active category Opencart

Please LogIn to Reply!

Post By: guest | 01-12-2013 07:57 Reply

Question

Hello everyone!
Im using OpenCart 1.4.9.1-clean install.

Im trying to change the bestsellers script so whenever a user browse's a certain category, the products in bestsellers to be from that category only! I hope explained that well!

To achieve that i've made some changes in :
/catalog/model/catalog/product.php

i've made some changes in function getBestSellerProducts(), but unfortunately i can't get the current category number right, can someone help/suggest something?
Thank you

Code: Select all
public function getBestSellerProducts($category_id){
   $limit=5;
      $product_data = $this->cache->get('product.bestseller.' . $this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $limit);
      
      if (!$product_data) {
         $product_data = array();
         
$query = $this->db->query("SELECT op.product_id, SUM(op.quantity),ptc.category_id 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_to_category` ptc ON (op.product_id = ptc.product_id) WHERE o.order_status_id > '0' and category_id='" . (int)$category_id . "' GROUP BY op.product_id ORDER BY total DESC LIMIT " . (int)$limit);         

         foreach ($query->rows as $result) {
            $product_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product p 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.product_id = '" . (int)$result['product_id'] . "' AND p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");
            
            if ($product_query->num_rows) {
               $product_data[] = $product_query->row;
            }
         }

         $this->cache->set('product.bestseller.' . $this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $limit, $product_data);
      }
      
      return $product_data;
   }

Answers

i was tryin to do the same have u succeeded in it? can u paste the correct query

thanks

You can get the current category ID from the path

Code: Select all
$path = $this->request->get['path'];
$cat_array = explode ("_", $path);
$cat_id = end($cat_array);

Of course you'll need to wrap this in
Code: Select all
if (isset($this->request->get['path'])) {
..
..
..
}

I tried the code above

Code: Select all
   $product_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN product_to_category p2c ON (p.product_id = p2c.product_id) WHERE p.product_id = '" . (int)$result['product_id'] . "' AND p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2c.category_id = '34'");


it works, but the bestseller will get stuck on the category_id 34.
I tried to change the id to a variable $category_id, but it doesnt work.

is there any way to get bestseller for each category?

Code: Select all
   $product_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN product_to_category p2c ON (p.product_id = p2c.product_id) WHERE p.product_id = '" . (int)$result['product_id'] . "' AND p.status = '1' AND p.date_available <= NOW() AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2c.category_id = '34'");


Please help me, thank you

Please try with the code:

Code: Select all
public function getBestSellerProductsbyCate($category_id,$limit){
          if ($this->customer->isLogged()) {
         $customer_group_id = $this->customer->getCustomerGroupId();
      } else {
         $customer_group_id = $this->config->get('config_customer_group_id');
      }   
            
      $product_data = $this->cache->get('product.bestseller.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id'). '.' . $customer_group_id . '.' . (int)$limit);

      if (!$product_data) {
         $product_data = array();
         
         $query = $this->db->query("SELECT op.product_id, COUNT(*) 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 (p.product_id = p2c.product_id) WHERE o.order_status_id > '0' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND p2c.category_id = '".$category_id."'  GROUP BY op.product_id ORDER BY total DESC LIMIT " . (int)$limit);
         
         foreach ($query->rows as $result) {       
            $product_data[$result['product_id']] = $this->getProduct($result['product_id']);
         }
         
         $this->cache->set('product.bestseller.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id'). '.' . $customer_group_id . '.' . (int)$limit, $product_data);
      }
      
      return $product_data;
       }

Tags:

opencart bestsellers

Reply Or Comment!
Please LogIn to Reply!