Display products by reference with natural sort.

[jeromecollection]

hello

I'm trying to make the product-sort.tpl display products by reference with natural sort.

i mean 
 i would like to order by like that:

1a
2a
3a
15a
33a
 

Not

1a
15a
2a
3a
33a

if someone can help me

Thanks you

 

 

Edited July 13, 2019 by jeromecollection (see edit history)

[Kert L]

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 July 4, 2019 by Kert L
More details (see edit history)

[jeromecollection]

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

        $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
        ));
    }

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

thank you

Edited July 5, 2019 by jeromecollection (see edit history)

[Kert L]

First of create override class: http://doc.prestashop.com/pages/viewpage.action?pageId=51184698

Then you can use the same if statement as I previously commented but this time you have to check "$this->orderBy" not "$value" and do it after smarty variables have been assigned.

 

[jeromecollection]

hello
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 !!!!

[Kert L]

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

<?php

class FrontController extends FrontControllerCore
{
    public function productSort()
    {
        parent::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 July 7, 2019 by Kert L
comments (see edit history)

[jeromecollection]

Hello that's working !!!!!!!!!!!!!!!

perfect !

Thank you very much !🍻

Edited July 13, 2019 by jeromecollection (see edit history)

[jeromecollection]

finaly ... not working ....

i've got a fatal error .... when i delete cache ...
when i delete FrontController.php (overide) - no fatal error but not natural sort 

https://www.jeromecollection.com/26-bosnie-herzegovine?orderby=reference&orderway=asc

 

[Kert L]

Can you turn on Debug mode, and show us the error ?

[jeromecollection]

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

 

[jeromecollection]

Category.php

<?php

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';
		else
			$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;
		}
		else
			$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);
	}
}

 

[Kert L]

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 

 

[jeromecollection]

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&orderway=asc)

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

Array
(
    [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 ! 

Category.php

[Kert L]

I'm sorry what PrestaShop version are you using?

[jeromecollection]

PS 1.6.1.17

[Kert L]

Ah yes, this is my fault.. sorry.

EDIT: I need to look into this, it seems like the query will be all messed up

Edited July 15, 2019 by Kert L (see edit history)

[jeromecollection]

yes true

[jeromecollection]

Please i realy need this

 

[Kert L]

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.