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!

You like the tuts and want to say "thank you"? Well, you can always feel free to donate:

  • http://www.danielgarciasanchez.eu/ Daniel

    Very very awesome!
    Thanks for sharing. You helped me a lot! It works like a charm!
    Best regards

  • Nathan

    Hello,
    Indeed, you explained what I’m looking for weeks.
    Alas, this does not visibly running on version 1.6?
    Indeed, when I click sorting by “Bestsellers> Worst” For example, this poster nothing. Some more products is displayed.

    Do you know why ? thank you very much

  • Albert

    Tried this in 1.6.2 and it didn’t work for me. Just a random sort order.

    Downloaded your files. Added Category.php to override/classes, added CategoryController.php to override/controllers/front, added the 2 lines of option values to my product-sort.tpl file and then I deleted the class_index.php file. So I think I did everything correct. Any ideas?

  • raknjak

    Has this ever worked with layered navigation? Because without it this is quite useless wouldn’t you agree? Half the work = no work.

  • Peter PanPan

    In case you use Blocklayered Module, your method can be done BUT using different files

    The QUERY has to be modified into an override of “blocklayered.php” into the function getProductByFilters line 1779.

    And the value : 8 => ‘sales’ has to be added into an override of ./classes/Tools.php
    into the function getProductsOrder

    I am trying now these overrides…

    • lupillus

      Hi, can you please explain more how to do it if using Blocklayered module? Thanks!

      • http://thecrewonline.net/ The Crew Game

        Well, he actually did. Edit sql query as same as in tutorial but in blocklayered.php in getProductByFilters and edit Tools.php in getProductsOrder to add 8 => ‘sales’. Everything works.

  • iLifehackNL

    Hi there. I tried your tutorial (version 1.6) and want to sort by manufacturer name. For example the manufacturer name = “John Doe” and I want to sort it with “Doe”

    I added another column in my database called “sortname”. But I still have not succeeded in sorting on this column. Any tips on how to do this?

    Thank you in advance.

    • NemoPS

      Hard to tell. You have to make sure the sortname column is grabbed at the time products are retrieved in the database, and it’s used in the ORDER BY clause as well

  • http://sancolgates.wordpress.com sancoLgates

    Hi, Nemo is it works for PS. 1.6.0.9 ?
    i don’t have any difference…

  • moy2010

    Hi, Nemo :). Do you think it would be possible to create a sort based on product rating from productcomments module?

    • NemoPS

      Hey!
      Yeah, sure. Actually that’s a good idea for a new tut :D

  • Petr Pánek

    To properly implement this in back office I just needed to do another change.
    In file AdminPPreferencesController is defined array PS_PRODUCTS_ORDER_BY (row 122, in version 1.6.0.13). It need to be extended to know about your new sorting options. Just add the new line in the array like:
    array(‘id’ => ‘8’, ‘name’ => ‘whatever’)

    So the whole definition is like:
    ‘PS_PRODUCTS_ORDER_BY’ => array(
    ‘title’ => $this->l(‘Default order by’),
    ‘hint’ => $this->l(‘The order in which products are displayed in the product list.’),
    ‘type’ => ‘select’,
    ‘list’ => array(
    array(‘id’ => ‘0’, ‘name’ => $this->l(‘Product name’)),
    array(‘id’ => ‘1’, ‘name’ => $this->l(‘Product price’)),
    array(‘id’ => ‘2’, ‘name’ => $this->l(‘Product add date’)),
    array(‘id’ => ‘3’, ‘name’ => $this->l(‘Product modified date’)),
    array(‘id’ => ‘4’, ‘name’ => $this->l(‘Position inside category’)),
    array(‘id’ => ‘5’, ‘name’ => $this->l(‘Manufacturer’)),
    array(‘id’ => ‘6’, ‘name’ => $this->l(‘Product quantity’)),
    array(‘id’ => ‘7’, ‘name’ => $this->l(‘Product reference’)),
    array(‘id’ => ‘8’, ‘name’ => ‘whatever’)

  • Petr Pánek

    In Prestashop 1.6.0.13 (I just tested this version) the override of productSort() method need to be done in FrontController.php (where the product sort() method is original defined). It doesn`t work in CategoryController.php.

    Thank`s a lot for this guide, I tried to implement such a functionality but as a newbie I haven`t chance to do it properly.

  • vishnu

    Is there a way to sort/filter by attibutes such as size: small, medium, large??

    • NemoPS
      • Vishnu

        Thanks for your quick response. But the issue i have is, i have a mobile theme which on smaller screens blocks the left column block where layered navigations is hooked too. So i wanted to include the sort by size option in the dropdown.

Store Top Sales

You like the tuts and want to say "thank you"? Well, you can always feel free to donate: