Jump to content

Display products by reference with natural sort.

Recommended Posts

You could override Tools::getProductsOrder, if "$type" is reference add natural order sql and return it.


* Add natural sorting to reference
if('reference' === $value) {
  return "LENGTH($order_by_prefix$value), $order_by_prefix$value";

EDIT: This is assuming you are using blocklayered. Otherwise i would look at FrontController method productSort()

Edited by Kert L
More details (see edit history)
Link to comment
Share on other sites

Thank you for answer

i dont use blocklayered. 

part of FontController.php

  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];

            '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

i don't realy know what exactly i need to modifiy.

thank you

Edited by jeromecollection (see edit history)
Link to comment
Share on other sites

i create FontController.php in /override/controllers/front/

/** * Add natural sorting to reference */ if('reference' === $value) { return "LENGTH($order_by_prefix$value), $order_by_prefix$value"; }

but after ?

sorry i'm not specialist :(

Per advance thank you for your time !!!!

Link to comment
Share on other sites

You have to create an override:
/override/classes/controller/FrontController.php ( Because FrontController lives in classes/controller folder )


class FrontController extends FrontControllerCore
    public function productSort()

         * Add natural sorting to reference
        if('reference' === $this->orderBy) {
            $this->orderBy = " LENGTH($this->orderBy), $this->orderBy";


Delete cache and you are good to go. 

NB: I only tested this on category view.

Edited by Kert L
comments (see edit history)
Link to comment
Share on other sites

i check .

Fatal error : because i use another override 

overide / classes / Category.php (to put out of stock item at end of the pages) .. 

finaly i rename this files .. and now not fatal error but also not sort by natural order .

files need to be at : override/controllers/front/FrontController.php 
if i put it here override/controllers/FrontController.php  no fatal error but also nothing happen


Link to comment
Share on other sites



class Category extends CategoryCore
	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';
			$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');

		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, product_attribute_shop.`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`, image_shop.`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 (pa.id_product_attribute IS NULL OR product_attribute_shop.id_shop='.(int)$context->shop->id.') 
				AND (i.id_image IS NULL OR image_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 : '');

		if ($random === true)
			$sql .= ' ORDER BY RAND()';
			$sql .= ' LIMIT 0, '.(int)$random_number_products;
			$sql .= ' ORDER BY stock.quantity>0 desc, '.(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 (!$result)
			return array();

		return Product::getProductsProperties($id_lang, $result);


Link to comment
Share on other sites

Other overrides are not a problem. The problem is in your Category.php override file.

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

Copy this and replace it in your Category.php file. It seems like you have some weird character between function name and parenthesis. (...in_arrayHERE($context...)


And FrontController.php override is not here: override/controllers/front/FrontController.php 

it needs to be here: override/classes/controller/FrontController.php 


Link to comment
Share on other sites

1)i copy and replace 
(realy i don't see difference with your code 😳)

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

in override/classes/Category.php

2)i retry debug mode: 

define('_PS_MODE_DEV_', false);

when i launch page with fatal error (https://www.jeromecollection.com/26-bosnie-herzegovine?orderby=reference&amp;amp;orderway=asc)

i can see start op the page  (more than 43000 lines :

    [0] => Array
            [file] => /home/deschamps/public_html/override/classes/Category.php
            [line] => 41
            [function] => displayError
            [class] => ToolsCore
            [type] => ::
            [args] => Array


3) i put FrontController.php in override/controllers/FrontController.php


Realy thank you for your time ! 


Link to comment
Share on other sites

Hi, sorry for late reply.

Your Category.php file is from another PrestaShop version?

And as for your problem, it turns out MySQL does not have a natural sort. You would have to make some sort of "function" in your query to make it happen.

Another option is to make custom development to sort in php using php-s naturalsort, but this is more complicated. Maybe the option will be presented in some version of PrestaShop.

For now, i'm afraid i can not help you further.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...