Sort Categories Alphabetically Mod (1.4.3 tested)

xmurph · October 16, 2011

Description:

modification to the core to insert a button in backoffice to sort alphabetically categories and subcategories in front-office.

License:

This code is realized by Enorasy programmer and released under the Gnu General Public License by Xmurph you can freely modify, redistribute, copy. Nobody allowed to sell it.

Note:

Prestashop's team is strongly encuraged to insert this code in next Prestashop's release due is unacceptable that an e-commerce software does not sort alphabetically categories in front-end. there are a lot of sites out there that have hundreds or thousands of categories and is unacceptable to sort manually with backend drag'n drop to have a front end alphabetically sorted categories. this is e-commerce put your brain in the hand of the shopper!

Pay attention:

this code is developed for Prestashop 1.4.3 but should work fine on other versions, comments on other versions are welcome, you're encouraged to post modifications if needed to make it working on new versions

Instructions:

1. Go to MyPhpAdmin of your website > Select the prestashop database > Select to execute SQL code

CREATE TABLE ps_order_alpha (
orderAlphaId INT NOT NULL PRIMARY KEY,
orderAlphaValue VARCHAR(255)
) DEFAULT CHARACTER SET utf8;
INSERT INTO ps_order_alpha SET
orderAlphaId = 1,
orderAlphaValue ='1';

2 edit /modules/blockcategories/blockcategories.php

near line ~156 : find this code:

$maxdepth = Configuration::get('BLOCK_CATEG_MAX_DEPTH');
  if (!$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
   SELECT c.id_parent, c.id_category, cl.name, cl.description, cl.link_rewrite
   FROM `'._DB_PREFIX_.'category` c
   LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category` AND `id_lang` = '.$id_lang.')
   LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = c.`id_category`)
   WHERE (c.`active` = 1 OR c.`id_category` = 1)
   '.((int)($maxdepth) != 0 ? ' AND `level_depth` <= '.(int)($maxdepth) : '').'
   AND cg.`id_group` IN ('.pSQL($groups).')
   GROUP BY id_category
   ORDER BY `level_depth` ASC, c.`position` ASC')
  )
   return;

replace with following:

// Connect to the table we have created to check if Alphabetically order is true
  $db = Db::getInstance(_PS_USE_SQL_SLAVE_);
  $sql = 'SELECT * FROM '._DB_PREFIX_.'order_alpha WHERE orderAlphaId = 1';
  if ($results = Db::getInstance()->ExecuteS($sql))
   foreach ($results as $row)
   $alpha = $row['orderAlphaValue'];
  // If Alphabetically order is true
  // Order Alphabetically Categoriew and Subcategories
  if ($alpha == '1') {
  $maxdepth = Configuration::get('BLOCK_CATEG_MAX_DEPTH');
  if (!$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
   SELECT c.id_parent, c.id_category, cl.name, cl.description, cl.link_rewrite
   FROM `'._DB_PREFIX_.'category` c
   LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category` AND `id_lang` = '.$id_lang.')
   LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = c.`id_category`)
   WHERE (c.`active` = 1 OR c.`id_category` = 1)
   '.((int)($maxdepth) != 0 ? ' AND `level_depth` <= '.(int)($maxdepth) : '').'
   AND cg.`id_group` IN ('.pSQL($groups).')
   GROUP BY id_category
   ORDER BY `level_depth` ASC, cl.`name` ASC')
  )
   return;
  // Else order by backoffice position (Default Behaviour)
  } else {
  $maxdepth = Configuration::get('BLOCK_CATEG_MAX_DEPTH');
  if (!$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
   SELECT c.id_parent, c.id_category, cl.name, cl.description, cl.link_rewrite
   FROM `'._DB_PREFIX_.'category` c
   LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category` AND `id_lang` = '.$id_lang.')
   LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = c.`id_category`)
   WHERE (c.`active` = 1 OR c.`id_category` = 1)
   '.((int)($maxdepth) != 0 ? ' AND `level_depth` <= '.(int)($maxdepth) : '').'
   AND cg.`id_group` IN ('.pSQL($groups).')
   GROUP BY id_category
   ORDER BY `level_depth` ASC, c.`position` ASC')
  )
   return;
  }

near line ~255 : find this code

$maxdepth = Configuration::get('BLOCK_CATEG_MAX_DEPTH');
  if (!$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
   SELECT c.id_parent, c.id_category, cl.name, cl.description, cl.link_rewrite
   FROM `'._DB_PREFIX_.'category` c
   LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category` AND `id_lang` = '.$id_lang.')
   LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = c.`id_category`)
   WHERE (c.`active` = 1 OR c.`id_category` = 1)
   '.((int)($maxdepth) != 0 ? ' AND `level_depth` <= '.(int)($maxdepth) : '').'
   AND cg.`id_group` IN ('.pSQL($groups).')
   ORDER BY `level_depth` ASC, c.`position` ASC')
  )
   return;

replace with this:

$db = Db::getInstance(_PS_USE_SQL_SLAVE_);
  $sql = 'SELECT * FROM '._DB_PREFIX_.'order_alpha WHERE orderAlphaId = 1';
  if ($results = Db::getInstance()->ExecuteS($sql))
   foreach ($results as $row)
   $alpha = $row['orderAlphaValue'];

  if ($alpha == '1') {

  $maxdepth = Configuration::get('BLOCK_CATEG_MAX_DEPTH');
  if (!$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
   SELECT c.id_parent, c.id_category, cl.name, cl.description, cl.link_rewrite
   FROM `'._DB_PREFIX_.'category` c
   LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category` AND `id_lang` = '.$id_lang.')
   LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = c.`id_category`)
   WHERE (c.`active` = 1 OR c.`id_category` = 1)
   '.((int)($maxdepth) != 0 ? ' AND `level_depth` <= '.(int)($maxdepth) : '').'
   AND cg.`id_group` IN ('.pSQL($groups).')
   ORDER BY `level_depth` ASC, cl.`name` ASC')
  )
   return;
  } else {
  $maxdepth = Configuration::get('BLOCK_CATEG_MAX_DEPTH');   if (!$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
   SELECT c.id_parent, c.id_category, cl.name, cl.description, cl.link_rewrite
   FROM `'._DB_PREFIX_.'category` c
   LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category` AND `id_lang` = '.$id_lang.')
   LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = c.`id_category`)
   WHERE (c.`active` = 1 OR c.`id_category` = 1)
   '.((int)($maxdepth) != 0 ? ' AND `level_depth` <= '.(int)($maxdepth) : '').'
   AND cg.`id_group` IN ('.pSQL($groups).')
   ORDER BY `level_depth` ASC, c.`position` ASC')
  )
   return;
  }

3 edit file: classes/Category.php

near line 375 find this code:

$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
   SELECT *
   FROM `'._DB_PREFIX_.'category` c
   LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON c.`id_category` = cl.`id_category`
   WHERE 1 '.$sql_filter.' '.($id_lang ? ' AND `id_lang` = '.(int)($id_lang) : '').'
   '.($active ? 'AND `active` = 1' : '').'
   '.(!$id_lang ? 'GROUP BY c.id_category' : '').'
   '.($sql_sort != '' ? $sql_sort : 'ORDER BY c.`level_depth` ASC, c.`position` ASC').'
   '.($sql_limit != '' ? $sql_limit : '')
   );

replace with this:

// Connect to the table we have created to check if Alphabetically order is true
  $db = Db::getInstance(_PS_USE_SQL_SLAVE_);
  $sql = 'SELECT * FROM '._DB_PREFIX_.'order_alpha WHERE orderAlphaId = 1';
  if ($results = Db::getInstance()->ExecuteS($sql))
   foreach ($results as $row)
   $alpha = $row['orderAlphaValue'];
  // If Alphabetically order is true
  // Order Alphabetically Categoriew and Subcategories
  if ($alpha == '1') {
   $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
   SELECT *
   FROM `'._DB_PREFIX_.'category` c
   LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON c.`id_category` = cl.`id_category`
   WHERE 1 '.$sql_filter.' '.($id_lang ? ' AND `id_lang` = '.(int)($id_lang) : '').'
   '.($active ? 'AND `active` = 1' : '').'
   '.(!$id_lang ? 'GROUP BY c.id_category' : '').'
   '.($sql_sort != '' ? $sql_sort : 'ORDER BY c.`level_depth` ASC, cl.`name` ASC').'
   '.($sql_limit != '' ? $sql_limit : '')
   );

  } else {
   $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
   SELECT *
   FROM `'._DB_PREFIX_.'category` c
   LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON c.`id_category` = cl.`id_category`
   WHERE 1 '.$sql_filter.' '.($id_lang ? ' AND `id_lang` = '.(int)($id_lang) : '').'
   '.($active ? 'AND `active` = 1' : '').'
   '.(!$id_lang ? 'GROUP BY c.id_category' : '').'
   '.($sql_sort != '' ? $sql_sort : 'ORDER BY c.`level_depth` ASC, c.`position` ASC').'
   '.($sql_limit != '' ? $sql_limit : '')
   );
  }

near line 423 find this code:

$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
   SELECT *
   FROM `'._DB_PREFIX_.'category` c
   LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON c.`id_category` = cl.`id_category`
   WHERE 1 '.$sql_filter.' '.($id_lang ? ' AND id_parent=1 AND `id_lang` = '.(int)($id_lang) : '').'
   '.($active ? 'AND `active` = 1' : '').'
   '.(!$id_lang ? 'GROUP BY c.id_category' : '').'
   '.($sql_sort != '' ? $sql_sort : 'ORDER BY c.`level_depth` ASC, c.`position` ASC').'
   '.($sql_limit != '' ? $sql_limit : '')
   );

replace with this:

// Connect to the table we have created to check if Alphabetically order is true
  $db = Db::getInstance(_PS_USE_SQL_SLAVE_);
  $sql = 'SELECT * FROM '._DB_PREFIX_.'order_alpha WHERE orderAlphaId = 1';
  if ($results = Db::getInstance()->ExecuteS($sql))
   foreach ($results as $row)
   $alpha = $row['orderAlphaValue'];
  // If Alphabetically order is true
  // Order Alphabetically Categoriew and Subcategories
  if ($alpha == '1') {
   $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
   SELECT *
   FROM `'._DB_PREFIX_.'category` c
   LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON c.`id_category` = cl.`id_category`
   WHERE 1 '.$sql_filter.' '.($id_lang ? ' AND id_parent=1 AND `id_lang` = '.(int)($id_lang) : '').'
   '.($active ? 'AND `active` = 1' : '').'
   '.(!$id_lang ? 'GROUP BY c.id_category' : '').'
   '.($sql_sort != '' ? $sql_sort : 'ORDER BY c.`level_depth` ASC, cl.`name` ASC').'
   '.($sql_limit != '' ? $sql_limit : '')
   );
  } else {
   $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
   SELECT *
   FROM `'._DB_PREFIX_.'category` c
   LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON c.`id_category` = cl.`id_category`
   WHERE 1 '.$sql_filter.' '.($id_lang ? ' AND id_parent=1 AND `id_lang` = '.(int)($id_lang) : '').'
   '.($active ? 'AND `active` = 1' : '').'
   '.(!$id_lang ? 'GROUP BY c.id_category' : '').'
   '.($sql_sort != '' ? $sql_sort : 'ORDER BY c.`level_depth` ASC, c.`position` ASC').'
   '.($sql_limit != '' ? $sql_limit : '')
   );
  }

near line 469 find this code:

return Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
 SELECT c.`id_category`, cl.`name`
 FROM `'._DB_PREFIX_.'category` c
 LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category`)
 WHERE cl.`id_lang` = '.(int)($id_lang).'
 ORDER BY c.`position`');

replace with this:

// Connect to the table we have created to check if Alphabetically order is true
  $db = Db::getInstance(_PS_USE_SQL_SLAVE_);
  $sql = 'SELECT * FROM '._DB_PREFIX_.'order_alpha WHERE orderAlphaId = 1';
  if ($results = Db::getInstance()->ExecuteS($sql))
   foreach ($results as $row)
   $alpha = $row['orderAlphaValue'];
  // If Alphabetically order is true
  // Order Alphabetically Categoriew and Subcategories
  if ($alpha == '1') {
 return Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
 SELECT c.`id_category`, cl.`name`
 FROM `'._DB_PREFIX_.'category` c
 LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category`)
 WHERE cl.`id_lang` = '.(int)($id_lang).'
 ORDER BY cl.`name`');
  } else {
 return Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
 SELECT c.`id_category`, cl.`name`
 FROM `'._DB_PREFIX_.'category` c
 LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category`)
 WHERE cl.`id_lang` = '.(int)($id_lang).'
 ORDER BY c.`position`');
  }

near line 511 find this code:

$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
 SELECT c.*, cl.id_lang, cl.name, cl.description, cl.link_rewrite, cl.meta_title, cl.meta_keywords, cl.meta_description
 FROM `'._DB_PREFIX_.'category` c
 LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category` AND `id_lang` = '.(int)($id_lang).')
 LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = c.`id_category`)
 WHERE `id_parent` = '.(int)($this->id).'
 '.($active ? 'AND `active` = 1' : '').'
 AND cg.`id_group` '.$sqlGroups.'
 GROUP BY c.`id_category`
 ORDER BY `level_depth` ASC, c.`position` ASC');

replace with this:

// Connect to the table we have created to check if Alphabetically order is true
  $db = Db::getInstance(_PS_USE_SQL_SLAVE_);
  $sql = 'SELECT * FROM '._DB_PREFIX_.'order_alpha WHERE orderAlphaId = 1';
  if ($results = Db::getInstance()->ExecuteS($sql))
   foreach ($results as $row)
   $alpha = $row['orderAlphaValue'];
  // If Alphabetically order is true
  // Order Alphabetically Categoriew and Subcategories
  if ($alpha == '1') {
 $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
 SELECT c.*, cl.id_lang, cl.name, cl.description, cl.link_rewrite, cl.meta_title, cl.meta_keywords, cl.meta_description
 FROM `'._DB_PREFIX_.'category` c
 LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category` AND `id_lang` = '.(int)($id_lang).')
 LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = c.`id_category`)
 WHERE `id_parent` = '.(int)($this->id).'
 '.($active ? 'AND `active` = 1' : '').'
 AND cg.`id_group` '.$sqlGroups.'
 GROUP BY c.`id_category`
 ORDER BY `level_depth` ASC, cl.`name` ASC');
  } else {
 $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
 SELECT c.*, cl.id_lang, cl.name, cl.description, cl.link_rewrite, cl.meta_title, cl.meta_keywords, cl.meta_description
 FROM `'._DB_PREFIX_.'category` c
 LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category` AND `id_lang` = '.(int)($id_lang).')
 LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = c.`id_category`)
 WHERE `id_parent` = '.(int)($this->id).'
 '.($active ? 'AND `active` = 1' : '').'
 AND cg.`id_group` '.$sqlGroups.'
 GROUP BY c.`id_category`
 ORDER BY `level_depth` ASC, c.`position` ASC');
  }

4 edit /youradmin/tabs/AdminCatalog.php

near line 193 insert this code:

// Connect to db to check wich order is selected
  $db = Db::getInstance(_PS_USE_SQL_SLAVE_);
  $sql = 'SELECT * FROM '._DB_PREFIX_.'order_alpha WHERE orderAlphaId = 1';
  if ($results = Db::getInstance()->ExecuteS($sql))
   foreach ($results as $row)
   $alpha = $row['orderAlphaValue'];
   $oselected = '';
   $oneselected = '';
  if ($alpha == 0 ) { $oselected = 'selected="selected"'; }
  if ($alpha == 1 ) { $oneselected = 'selected="selected"'; }
  // Create the form to select order type
  echo '<div style="margin-bottom:20px;"><form action="" method="post">
   <select name="alphaOrder" id="alphaOrder">
 <option value="0" '. $oselected .'>Default Position</option>
 <option value="1" '. $oneselected .'>Alphabetically</option>
   </select>
   <input class="button" type="submit" value="Save" name="orderAction" />
   <label for="order">Sort categories alphabetically: </label>
   </form></div>';

xmurph · November 8, 2011

ok tested also on Prestashop 1.4.5.1

DisturbedGoW · March 3, 2012

Could you not give out the edited files to the users so we can just insert them?

In my case the lines are complitely wrong, and I can´t find the desired code, (prestashop 1.4.4.1).

an I expect any help?

Best regards,

Dist

tawanghar · June 7, 2012

Thank yo u for amazing fix, I am on 1.4.8 and the Product Sub Categories works on the Cat page, but on the Block It is still not in the correct order what should I do?

http://www.caltexrecords.com/en/38-persian-music-artist

tawanghar · June 7, 2012

I got this fixed on most, the only issue I am having, When you edit product, the categories are not sorted, under product edit page, any Suggestions?

orwell · August 10, 2012

This module did it for me !

http://www.prestashop.com/forums/topic/166329-sadan-sorteres-kategorierne-alfabetisk-i-version-14/

Sign In

Sort Categories Alphabetically Mod (1.4.3 tested)

Recommended Posts

xmurph

Link to comment

Share on other sites

xmurph

Link to comment

Share on other sites

DisturbedGoW

Link to comment

Share on other sites

tawanghar

Link to comment

Share on other sites

tawanghar

Link to comment

Share on other sites

orwell

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Browse

Activity

Go back to prestashop.com