Accessories on product.tpl sorted by price

[Martin89]

is it possible for my product accessories to sort by price?

So I want on my product.tpl the product accessories selecting on price from low to high...

Hope someone can help me with this...

[optiplex1]

it so simple you use acceding & decceding query.

[Martin89]

Hi optiplex,

Thanks for your fast reply, can you explain what you mean. Cause i search for hours but cant find how i can fix this...

I think that i have to make some changes into product.php and product.tpl but i cant find where the ORDER BY query stands?

[Martin89]

Also into the backoffice i cant find where the ORDER BY for accesories stands into the adminproduct.php

It looks like the accesories standard sort by id_product ?! I would like to change this on price...

[Martin89]

Is there someone who knows a solution for sorting accessories on the product page?? I would like to sort the accessories by price...

[Jeo]

Hi,

 

I've sorted the accessories on catalog order (manually adjustable):

 

<- Version 1.4.8.2 ->

 

Open Product.php in the classes folder with an editor

 

Go to line 2260 and change the code into:

 

 

/**

* Get product accessories

*

* @param integer $id_lang Language id

* @return array Product accessories

*/

public function getAccessories($id_lang, $active = true)

{

$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('

SELECT p.*, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, p.`ean13`, p.`upc`,

i.`id_image`, il.`legend`, t.`rate`, m.`name` as manufacturer_name, cl.`name` AS category_default, cp.`position` as position, DATEDIFF(p.`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

FROM `'._DB_PREFIX_.'accessory`

LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = `id_product_2`

LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.(int)($id_lang).')

LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = '.(int)($id_lang).')

LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)

LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)($id_lang).')

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

LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`

AND tr.`id_country` = '.(int)Country::getDefaultCountryId().'

AND tr.`id_state` = 0)

LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = tr.`id_tax`)

LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (p.`id_product` = cp.`id_product`)

WHERE `id_product_1` = '.(int)($this->id).'

'.($active ? 'AND p.`active` = 1' : '') .' ORDER BY `position` ASC');

 

if (!$result)

return false;

 

return $this->getProductsProperties($id_lang, $result);

}

 

I've added the cp.`position` as position in the SELECT

added LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (p.`id_product` = cp.`id_product`) to get the category positions from category_product table

Added the ORDER BY position ASC at the end.

 

---

 

If you wish to sort by price, change code to:

 

 

public function getAccessories($id_lang, $active = true)

{

$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('

SELECT p.*, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, p.`ean13`, p.`upc`,

i.`id_image`, il.`legend`, t.`rate`, m.`name` as manufacturer_name, cl.`name` AS category_default, DATEDIFF(p.`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

FROM `'._DB_PREFIX_.'accessory`

LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = `id_product_2`

LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.(int)($id_lang).')

LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = '.(int)($id_lang).')

LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)

LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)($id_lang).')

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

LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`

AND tr.`id_country` = '.(int)Country::getDefaultCountryId().'

AND tr.`id_state` = 0)

LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = tr.`id_tax`)

WHERE `id_product_1` = '.(int)($this->id).'

'.($active ? 'AND p.`active` = 1' : '') .' ORDER BY p.`price` ASC');

 

if (!$result)

return false;

 

return $this->getProductsProperties($id_lang, $result);

}

 

Only added the ORDER BY p.`price` ASC at the end

 

Good luck!

[Nikpro]

Hi,

 

I've sorted the accessories on catalog order (manually adjustable):

 

 

 

return $this->getProductsProperties($id_lang, $result);

}

 

Only added the ORDER BY p.`price` ASC at the end

 

Good luck!

 

 

Help please  find solution for 1.6 version 

[zeisei]

I also need sorting for accessories in product page by position for PS 1.6.0.9