This tip will help you to optimize the operation speed of the function cleanPositions. This function is used in the standard import feature and it is loading the database pretty much due to a large number of ‘Update’ requests.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
public static function cleanPositions($id_category) { $return = true; $result = Db::getInstance()->executeS(' SELECT `id_product` FROM `'._DB_PREFIX_.'category_product` WHERE `id_category` = '.(int)$id_category.' ORDER BY `position` '); $total = count($result); for ($i = 0; $i < $total; $i++) $return &= Db::getInstance()->update('category_product', array( 'position' => $i, ), '`id_category` = '.(int)$id_category.' AND `id_product` = '.(int)$result[$i]['id_product']); return $return; } |
This can become a serious problem if there is a large number of products in the directory, while the updated function works much faster.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
public static function cleanPositions($id_category) { $result = Db::getInstance()->execute(' update `'._DB_PREFIX_.'category_product` cp1 join ( select id_category, id_product, @i := @i+1 new_position from `'._DB_PREFIX_.'category_product`, (select @i:=-1) temp where id_category = '.(int)$id_category.' order by position asc ) cp2 on cp1.id_category = cp2.id_category and cp1.id_product = cp2.id_product set cp1.position = cp2.new_position '); return $return; } |
Thanks for this solution to our friend and colleague Alexander Deynichenko.
The second method is proposed by Pavel Novitsky:
Create a procedure
|
1 2 3 4 5 6 7 |
DELIMITER // CREATE PROCEDURE updatePosition(IN category_id INT(10)) BEGIN SET @i:=-1; UPDATE `ps_category_product` SET `position` = ( SELECT @i := @i + 1 ) WHERE `id_category`= category_id; END // DELIMITER ; |
and run it in cleanPositions() as
|
1 |
Db::getInstance()->executeS('CALL updatePosition('.(int)$id_category.')'); |
Partner With Us
Looking for a partner to grow your business? We are the right company to bring your webstore to success.
Talk to Igor
lordbdp,
This code should replace Product::cleanPosition($id_category) i.e. it is best to place it in the file override / classes / Product.php.
Tks for all but where put the code lines ?
BR
Nice idea, but where put the codes plz ?
BR