Adding a ‘Sort by Sales’ Option to Prestashop

Offering the possibility to order products by sales when browsing categories is a great way to have your customers know about your best items. Let’s see how to add this to Prestashop!

Download Project Files

  • Version used: Prestashop 1.5.6

Introduction

In order to add the ‘sort by sales’ option to the product sort dropdown in Prestashop, a couple of files need to be changed. To be specific, we will first edit the template file responsible of displaying the options, product-sort.tpl, and then add 2 overrides: Category.php (class file, to add the new sort option to the query which grabs products) and CategoryController.php, to add the ‘sort by sales’ option to the valid list of ‘sort by’ values.

It looks longer than it is! Let’s get started!

Editing the product-sort template file

First, it is vital that we add the ‘sort by sales’ option (for both ascending and descending order, of course!) to our template. Therefore, open up product-sort.tpl which you can find in you theme’s folder. As always, I am using the default theme, so your code might be a little different!

Locate the following code:

	<option value="name:asc" {if $orderby eq 'name' AND $orderway eq 'asc'}selected="selected"{/if}>{l s='Product Name: A to Z'}</option>

Right before it, add:

	<option value="sales:desc" {if $orderby eq 'sales' AND $orderway eq 'desc'}selected="selected"{/if}>{l s='Sales: Best to Worst'}</option>
	<option value="sales:asc" {if $orderby eq 'sales' AND $orderway eq 'asc'}selected="selected"{/if}>{l s='Sales: Worst to Best'}</option>

Note: you can freely choose where to add those 2 lines, I have chosen the spot above the ‘Order by product name’, but you can really add the new options wherever you want, as long as they sit inside the <select> box.

What we did here was simply adding a visual reference so that customers can choose if they want to order by best sales, with best products first (first option), or worst first (second option). Of course, these options will not do anything now!

Adding the class override

Let’s get dirty now! We need to first add an override for the Category class, so that a new parameter (sales) is added to the ‘order by’ clause in the sql query which grabs products. If all what I am saying sounds alien to you, be sure you first understand the basics of MySQL (some great articles can be found here).

Therefore, go to override/classes/ and create a new file named Category.php. Then, open it up and add the following inside:

<?php

Class Category extends CategoryCore
{

}
?>

Note: you might want to read this article about using Prestashop Overrides to extend default functionalities if you never used them!

Then, to save us some trouble, we will copy the original getProducts() method of the Category class. It looks like the following, but be sure you copy your own, if you use a different Prestashop Version than mine! (1.5.6):


	public function getProducts($id_lang, $p, $n, $order_by = null, $order_way = null, $get_total = false, $active = true, $random = false, $random_number_products = 1, $check_access = true, Context $context = null)
	{
		if (!$context)
			$context = Context::getContext();
		if ($check_access && !$this->checkAccess($context->customer->id))
			return false;

		$front = true;
		if (!in_array($context->controller->controller_type, array('front', 'modulefront')))
			$front = false;

		if ($p < 1) $p = 1;

		if (empty($order_by))
			$order_by = 'position';
		else
			/* Fix for all modules which are now using lowercase values for 'orderBy' parameter */
			$order_by = strtolower($order_by);

		if (empty($order_way))
			$order_way = 'ASC';
		if ($order_by == 'id_product' || $order_by == 'date_add' || $order_by == 'date_upd')
			$order_by_prefix = 'p';
		elseif ($order_by == 'name')
			$order_by_prefix = 'pl';
		elseif ($order_by == 'manufacturer')
		{
			$order_by_prefix = 'm';
			$order_by = 'name';
		}
		elseif ($order_by == 'position')
			$order_by_prefix = 'cp';

		if ($order_by == 'price')
			$order_by = 'orderprice';

		if (!Validate::isBool($active) || !Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way))
			die (Tools::displayError());

		$id_supplier = (int)Tools::getValue('id_supplier');

		/* Return only the number of products */
		if ($get_total)
		{
			$sql = 'SELECT COUNT(cp.`id_product`) AS total
					FROM `'._DB_PREFIX_.'product` p
					'.Shop::addSqlAssociation('product', 'p').'
					LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON p.`id_product` = cp.`id_product`
					WHERE cp.`id_category` = '.(int)$this->id.
					($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '').
					($active ? ' AND product_shop.`active` = 1' : '').
					($id_supplier ? 'AND p.id_supplier = '.(int)$id_supplier : '');
			return (int)Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql);
		}

		$sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity, pl.`description`, pl.`description_short`, pl.`available_now`,
					pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, MAX(image_shop.`id_image`) id_image,
					il.`legend`, m.`name` AS manufacturer_name, cl.`name` AS category_default,
					DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(),
					INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).'
						DAY)) > 0 AS new, product_shop.price AS orderprice
				FROM `'._DB_PREFIX_.'category_product` cp
				LEFT JOIN `'._DB_PREFIX_.'product` p
					ON p.`id_product` = cp.`id_product`
				'.Shop::addSqlAssociation('product', 'p').'
				LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa
				ON (p.`id_product` = pa.`id_product`)
				'.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1').'
				'.Product::sqlStock('p', 'product_attribute_shop', false, $context->shop).'
				LEFT JOIN `'._DB_PREFIX_.'category_lang` cl
					ON (product_shop.`id_category_default` = cl.`id_category`
					AND cl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('cl').')
				LEFT JOIN `'._DB_PREFIX_.'product_lang` pl
					ON (p.`id_product` = pl.`id_product`
					AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').')
				LEFT JOIN `'._DB_PREFIX_.'image` i
					ON (i.`id_product` = p.`id_product`)'.
				Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1').'
				LEFT JOIN `'._DB_PREFIX_.'image_lang` il
					ON (image_shop.`id_image` = il.`id_image`
					AND il.`id_lang` = '.(int)$id_lang.')
				LEFT JOIN `'._DB_PREFIX_.'manufacturer` m
					ON m.`id_manufacturer` = p.`id_manufacturer`
				WHERE product_shop.`id_shop` = '.(int)$context->shop->id.'
					AND cp.`id_category` = '.(int)$this->id
					.($active ? ' AND product_shop.`active` = 1' : '')
					.($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '')
					.($id_supplier ? ' AND p.id_supplier = '.(int)$id_supplier : '')
					.' GROUP BY product_shop.id_product';

		if ($random === true)
		{
			$sql .= ' ORDER BY RAND()';
			$sql .= ' LIMIT 0, '.(int)$random_number_products;
		}
		else
			$sql .= ' ORDER BY '.(isset($order_by_prefix) ? $order_by_prefix.'.' : '').'`'.pSQL($order_by).'` '.pSQL($order_way).'
			LIMIT '.(((int)$p - 1) * (int)$n).','.(int)$n;

		$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
		if ($order_by == 'orderprice')
			Tools::orderbyPrice($result, $order_way);

		if (!$result)
			return array();

		/* Modify SQL result */
		return Product::getProductsProperties($id_lang, $result);
	}

Take this method inside the override Class. Let’s get to the tricky part!

See that really long string for the $sql variable? We need to modify that one. First, locate the following, or in any case the last item of the SELECT statement:

product_shop.price AS orderprice

We need to grab one more value, like this:

product_shop.price AS orderprice, ps.quantity AS sales

Notice: we are referring to an alias (ps) which has not been defined yet. Then, for simplicity, we are calling this new parameter ‘sales’. If you remember, we previously used sales:desc and sales:asc for our select box. Well, for simplicity it’s better to call this new ‘order by’ parameter the same way across all files, so that we don’t need to add other parameters to refer to it when it changes!

Now, let’s join that ‘ps’ table we referred to in the select statement, which is product_sale! Locate the last join, which is probably this:

LEFT JOIN `'._DB_PREFIX_.'manufacturer` m
					ON m.`id_manufacturer` = p.`id_manufacturer`

and after it, but before the WHERE clause, add

LEFT JOIN '._DB_PREFIX_.'product_sale ps ON ps.id_product = product_shop.id_product

And we are done with the class. Again, nothing is working so far, let’s get to the last step and put everything together with a controller override!

Overriding the CategoryController

Let’s create another file, this time inside override/controllers/front. Name it CategoryController.php and add the following inside:

<?php
Class CategoryController extends CategoryControllerCore
{
?>

For this one, we must use a different approach. The native productSort() method is nowhere to be found inside CategoryControllerCore. So, why are we extending this? Because that class is extending another as well: FrontController. And this last one contains the method we need. If you are used to the rules of inheritance in OOP, you know that each class extending another has also access to all the parent’s methods, and can of course extend those as well. So, CategoryControllerCore, which extends FrontController, has access to its parent’s productSort. Thus (finally!) its own child, our new CategoryController know about it’s grandpa’s method too!

At this point, if you didn’t shoot your head, go to classes/controller/ and open the original FrontController.php. Locate its productSort() method:

	public function productSort()
	{
		// $this->orderBy = Tools::getProductsOrder('by', Tools::getValue('orderby'));
		// $this->orderWay = Tools::getProductsOrder('way', Tools::getValue('orderway'));
		// 'orderbydefault' => Tools::getProductsOrder('by'),
		// 'orderwayposition' => Tools::getProductsOrder('way'), // Deprecated: orderwayposition
		// 'orderwaydefault' => Tools::getProductsOrder('way'),

		$stock_management = Configuration::get('PS_STOCK_MANAGEMENT') ? true : false; // no display quantity order if stock management disabled
		$order_by_values = array(0 => 'name', 1 => 'price', 2 => 'date_add', 3 => 'date_upd', 4 => 'position', 5 => 'manufacturer_name', 6 => 'quantity', 7 => 'reference');
		$order_way_values = array(0 => 'asc', 1 => 'desc');
		$this->orderBy = Tools::strtolower(Tools::getValue('orderby', $order_by_values[(int)Configuration::get('PS_PRODUCTS_ORDER_BY')]));
		$this->orderWay = Tools::strtolower(Tools::getValue('orderway', $order_way_values[(int)Configuration::get('PS_PRODUCTS_ORDER_WAY')]));
		if (!in_array($this->orderBy, $order_by_values))
			$this->orderBy = $order_by_values[0];
		if (!in_array($this->orderWay, $order_way_values))
			$this->orderWay = $order_way_values[0];

		$this->context->smarty->assign(array(
			'orderby' => $this->orderBy,
			'orderway' => $this->orderWay,
			'orderbydefault' => $order_by_values[(int)Configuration::get('PS_PRODUCTS_ORDER_BY')],
			'orderwayposition' => $order_way_values[(int)Configuration::get('PS_PRODUCTS_ORDER_WAY')], // Deprecated: orderwayposition
			'orderwaydefault' => $order_way_values[(int)Configuration::get('PS_PRODUCTS_ORDER_WAY')],
			'stock_management' => (int)$stock_management));
	}

And paste it inside our new override:

Class CategoryController extends CategoryControllerCore
{
	public function productSort()
	{
		// $this->orderBy = Tools::getProductsOrder('by', Tools::getValue('orderby'));
		// $this->orderWay = Tools::getProductsOrder('way', Tools::getValue('orderway'));
		// 'orderbydefault' => Tools::getProductsOrder('by'),
		// 'orderwayposition' => Tools::getProductsOrder('way'), // Deprecated: orderwayposition
		// 'orderwaydefault' => Tools::getProductsOrder('way'),

		$stock_management = Configuration::get('PS_STOCK_MANAGEMENT') ? true : false; // no display quantity order if stock management disabled
		$order_by_values = array(0 => 'name', 1 => 'price', 2 => 'date_add', 3 => 'date_upd', 4 => 'position', 5 => 'manufacturer_name', 6 => 'quantity', 7 => 'reference');
		$order_way_values = array(0 => 'asc', 1 => 'desc');
		$this->orderBy = Tools::strtolower(Tools::getValue('orderby', $order_by_values[(int)Configuration::get('PS_PRODUCTS_ORDER_BY')]));
		$this->orderWay = Tools::strtolower(Tools::getValue('orderway', $order_way_values[(int)Configuration::get('PS_PRODUCTS_ORDER_WAY')]));
		if (!in_array($this->orderBy, $order_by_values))
			$this->orderBy = $order_by_values[0];
		if (!in_array($this->orderWay, $order_way_values))
			$this->orderWay = $order_way_values[0];

		$this->context->smarty->assign(array(
			'orderby' => $this->orderBy,
			'orderway' => $this->orderWay,
			'orderbydefault' => $order_by_values[(int)Configuration::get('PS_PRODUCTS_ORDER_BY')],
			'orderwayposition' => $order_way_values[(int)Configuration::get('PS_PRODUCTS_ORDER_WAY')], // Deprecated: orderwayposition
			'orderwaydefault' => $order_way_values[(int)Configuration::get('PS_PRODUCTS_ORDER_WAY')],
			'stock_management' => (int)$stock_management));
	}
}

We are almost done! Now pay a closer look at the $order_by_values variable. It’s an array containing all valid names for the ‘sort by’ box! Therefore, at this point we can simply add sales to it, as a last item:

	$order_by_values = array(0 => 'name', 1 => 'price', 2 => 'date_add', 3 => 'date_upd', 4 => 'position', 5 => 'manufacturer_name', 6 => 'quantity', 7 => 'reference', 8 => 'sales');

Do you now see why it is important to keep names uniform throughout all the code? Well, at this point we are done!

Go to cache/ and delete class_index.php. Navigate to a category page and test out the form, it should order your products by sales, and preserve changes while navigating through pages!

Important note: if you test out the new sorting options, be sure the orders for your tests are valid, as otherwise products bought will not add up to the quantity which is necessary to calculate the number of sales!

Need Prestashop Modules? Have a look at my Prestashop Addons Store!

Looking for quality PrestaShop Web Hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , , , , | Posted under PrestaShop | RSS 2.0

Author Spotlight

Fabio Porta

Fabio Porta

Fabio has been involved in web development and design since 2005, when launched his first website at the age of 16. He’s now highly skilled in both client and server side development, along with design, and since August 2012 runs a successful website about PrestaShop tutorials and Prestashop Modules called Nemo’s Post Scriptum, at http://nemops.com

3 Comments on Adding a ‘Sort by Sales’ Option to Prestashop

  1. Stefania says:

    Hello Fabio,
    do you think it’s possible to sort the products in order of expiration date of the datetime special price of products?

  2. Fabio Porta Fabio Porta says:

    Hi Stefania!
    I didn’t test it but you should be able to do it by joining ps_specific_price and ordering by “to” (editing the getProducts query in this case). Of course, you must add the ordering condition as explained in the tut, along with this

Leave a Reply

Your email address will not be published. Required fields are marked *


3 + 2 =

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>