There is often a need to display a table (list) of products in the category description for better promotion of an online store. This list consists of the most expensive product, the cheapest, the newest, and the most popular product. To implement this functionality, you can do it without modules by simply creating additional queries in the model, processing the data in the controller, and displaying it all in the template.
Product with the minimum price
First, let's create an additional function in the category model with a query that will retrieve the product with the minimum price.
After adding this function, you need to call it in the category controller and pass the data to the template.
// Getting the current category
$parts = explode('_', (string)$this->request->get['path']);
$category_id = (int)array_pop($parts);
// Calling the method from the model
$prodWithMinPrice = $this->model_catalog_category->getProductWithMinPrice($category_id);
// Passing the data to the template
$data['minPriceName'] = $prodWithMinPrice['name'];
$data['minPriceLink'] = $this->url->link('product/product', 'path=' . $this->request->get['path'] . '&product_id=' . $prodWithMinPrice['product_id']);
In the template (category.tpl), simply output the variables $minPriceName and $minPriceLink on the screen. This will display the product name and a link to it. To display the product price, add a discount check in the controller.
if(empty($prodWithMinPrice['curent_price'])){
$data['minPriceValue'] = $prodWithMinPrice['price'];
}else {
$data['minPriceValue'] = $prodWithMinPrice['curent_price'];
}
In the category.tpl file, display $minPriceValue to show the price.
Product with the maximum price
To display the product with the maximum price, you need to perform the same steps, but write a different function and a new query in the model.
public function getProductWithMaxPrice($category_id){
$query = $this->db->query("SELECT pd.name, IFNULL(ps.price,p.price) AS curent_price, p.*
FROM " . DB_PREFIX . "product p
INNER JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id)
INNER JOIN " . DB_PREFIX . "product_description pd ON (pd.product_id = p.product_id)
LEFT JOIN `" . DB_PREFIX . "product_special` ps ON (ps.product_id = p.product_id)
AND (ps.date_start <= CURDATE() AND (ps.date_end <=CURDATE() OR ps.date_end = '0000-00-00'))
WHERE p2c.category_id = '". (int) $category_id ."' AND p.status = '1' AND p.quantity > 0 AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'
ORDER BY curent_price DESC LIMIT 0,1 ");
return $query->row;
}
Most popular product
public function getProductWithMaxReviewed($category_id){
$query = $this->db->query("SELECT p.*, pd.name
FROM " . DB_PREFIX . "product p
INNER JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id)
INNER JOIN " . DB_PREFIX . "product_description pd ON (pd.product_id = p.product_id)
WHERE p2c.category_id = '". (int) $category_id ."'
AND p.status = '1' AND p.quantity > 0
AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'
ORDER BY p.viewed DESC LIMIT 0,1"
);
return $query->row;
}
Newest product
public function getProductNew($category_id){
$query = $this->db->query("SELECT pd.name, p.`date_added`, p.*
FROM " . DB_PREFIX . "product p
INNER JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id)
INNER JOIN " . DB_PREFIX . "product_description pd ON (pd.product_id = p.product_id)
WHERE p2c.category_id = '". (int) $category_id ."' AND p.status = '1' AND p.quantity > 0 AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'
ORDER BY p.`date_added` DESC LIMIT 0,1
");
return $query->row;
}
Ready-made module
We have packaged these developments into a module. If you need to display a table with a list of products (most expensive, cheapest, popular, and newest products) on your website, feel free to contact us on Telegram. We will help you install and customize the module for your online store.
