[Solved] Move product to the end in category using PHP

[hakeryk2]

Hello Devs,

How to change position of product in category to the end using PHP? 

Is there is any built in php solution or should I write my own?

 

EDIT: I wrote down something like this:

$nafo_products = Db::getInstance()->executeS('
                 SELECT `id_product` FROM `' . _DB_PREFIX_ . 'product`
                 WHERE `available_for_order` = 0 AND `active` = 1
            ');

    $nafo_products = array_column($nafo_products, 'id_product');

    foreach ($nafo_products as $id_product) {
        $product_categories = Product::getProductCategories((int)$id_product);
        foreach ($product_categories as $id_category) {
            $last_position = Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
              SELECT MAX(position)
              FROM ' . _DB_PREFIX_ . 'category_product
              WHERE id_category = ' . (int)$id_category . '
            ', true);

            $current_position = (int)Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
                SELECT position FROM ' . _DB_PREFIX_ . 'category_product
                WHERE id_category = ' . (int)$id_category . ' AND id_product = ' . (int)$id_product . '
                 ');

            // To improve performance (do not move products already moved)
            $counted_already_moved = count(Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
                SELECT p.id_product FROM  ' . _DB_PREFIX_ . 'product p
                LEFT JOIN ' . _DB_PREFIX_ . 'category_product cp ON (p.id_product = cp.id_product)
                WHERE cp.id_category = '.(int)$id_category.' AND p.active = 1 AND p.available_for_order = 0

                '));

            if (($last_position - $current_position - $counted_already_moved) >= 0 ){
                if (Product::setProductPositionInCategory($id_product, $id_category, $last_position)){
                    echo 'Moved ('.$id_product.') ' . Product::getProductName($id_product) . ' to the end of category' . $id_category . '.<br>';
                }
            } else {
                echo 'Product ('.$id_product.') ' . Product::getProductName($id_product) . ' is already at the end of ' . $id_category . ' <br>';
            }
        }
    }

 

And in Product.php override add this function which is not only usefull to move product to the end but to any position given. It is static remake setWsProductPosition which is only working for default id category. 

    public static function setProductPositionInCategory($id_product, $id_category, $position) {
        if ($position < 0)
            return false;

        $current_position = (int)Db::getInstance()->getvalue('
            SELECT position FROM ' . _DB_PREFIX_ . 'category_product
            WHERE id_category = ' . (int)$id_category . ' AND id_product = ' . (int)$id_product . '
        ');

        $products_in_category = Db::getInstance()->executeS('
          SELECT id_product FROM '._DB_PREFIX_.'category_product
            WHERE id_category = '. (int) $id_category.' ORDER BY position
        ');

        if ($current_position == $position || ($position + 1) > count($products_in_category))
            return false;

        $products_in_category = array_column($products_in_category, 'id_product');

        if ($current_position && isset($products_in_category[$current_position])) {
            $save = $products_in_category[$current_position];
            unset($products_in_category[$current_position]);
            array_splice($products_in_category, (int)$position, 0, $save);
        }

        foreach ($products_in_category as $new_position => $cat_id_product) {
            Db::getInstance()->update('category_product', array(
                'position' => $new_position,
            ), '`id_category` = '.(int)$id_category.' AND `id_product` = '.(int)$cat_id_product);
        }
        return true;
    }

This basically takes all of the not available for sale products and move them into the last position of each category they belongs. Put in cron.

 

Edited September 25, 2018 by hakeryk2
found a solution (see edit history)