Sort by price bug

[niosme]

Product sort by price when price overrides exist and also child products exist, throws wrong sorting results.
How to fix that?

Latest version of virtuemart in VirtueMart 4.2.2 10908 with Joomla! 3.10.12

[hazael]

administrator/components/com_virtuemart/models/product.php

Maybe in 748

Code Select Expand

case 'product_price':
(...)
break;
change to

Code Select Expand

case 'product_price':
    $orderBy = ' ORDER BY IF(pp.product_override_price > 0 AND pp.override = 1, pp.product_override_price, pp.product_price) ' . $filterOrderDir . ', p.`virtuemart_product_id` ' . $filterOrderDir;
    $ff_select_price = ' , IF(pp.override = 1 AND pp.product_override_price > 0, pp.product_override_price, pp.product_price) as product_price ';
    $joinPrice = TRUE;
    break;
It will work when the price is overwritten

However, if you mean sorting by rules for prices, which are located in the admin panel:
/administrator/index.php?option=com_virtuemart&view=calc
Sorting this is impossible, because these prices are generated directly in your template - as an end result.

[hazael]

I wrote a very simple script that will allow you to get around this problem 100% and Works only in Joomla 5


In the database in the table  virtuemart_product_prices Add the column final_price:

Code Select Expand

ALTER TABLE `#__virtuemart_product_prices`
ADD COLUMN `final_price` decimal(15,5) NOT NULL DEFAULT 0 AFTER `product_override_price`;

In
administrator/components/com_virtuemart/models/product.php

change

Code Select Expand

case 'product_price':
(...)
break;

to:

Code Select Expand

case 'product_price':
    $ff_select_price = ' , IF(pp.override, pp.product_override_price, pp.product_price) as product_price, pp.final_price ';
    $orderBy = ' ORDER BY pp.`final_price` ' . $filterOrderDir . ', p.`virtuemart_product_id` ' . $filterOrderDir;
    $joinPrice = TRUE;
    break;



Create a PHP file of any name that you need to place (preferably) in the main Joomla 5 directory:

Code Select Expand

<?php
define('_JEXEC', 1);
require_once __DIR__ . '/includes/app.php';
$app = Joomla\CMS\Factory::getApplication('site');

if (!class_exists('VmConfig')) {
    require(JPATH_ADMINISTRATOR . '/components/com_virtuemart/helpers/config.php');
}
VmConfig::loadConfig();
if (!class_exists('VmModel')) {
    require(JPATH_ADMINISTRATOR . '/components/com_virtuemart/helpers/vmmodel.php');
}

$db = JFactory::getDbo();
$query = $db->getQuery(true)
    ->select('virtuemart_product_id')
    ->from('#__virtuemart_products');

$db->setQuery($query);
$products = $db->loadColumn();
$productModel = VmModel::getModel('product');

foreach ($products as $product_id) {
    $product = $productModel->getProduct($product_id, true, true, true);
    $prices = $productModel->getPrice($product, [], 1);
    $finalPrice = isset($prices['salesPrice']) ? $prices['salesPrice'] : 0;
    $queryUpdate = $db->getQuery(true)
        ->update('#__virtuemart_product_prices')
        ->set('final_price = ' . (float)$finalPrice)
        ->where('virtuemart_product_id = ' . (int)$product_id);
    $db->setQuery($queryUpdate);
    $db->execute();
}

At the end, run this file on your website. It works 100%
You can also automatically run this file using the CRON command. Then the prices will always be as it should be.

This solution can also be used to display promotional prices or those with the lowest price from the last 30 days - what is required on many websites on which discounts are used. Here is an example of this solution:
https://forum.virtuemart.net/index.php?topic=152315.0


You cannot view this attachment.

[niosme]

I tested it but it doesnt work for child products without price.
Child products should have a price to show them in the right order :/

Also works in joomla 3 like this:

Code Select Expand

<?php

define('_JEXEC', 1);
define('JPATH_BASE', dirname(__FILE__));
require_once JPATH_BASE . '/includes/defines.php';
require_once JPATH_BASE . '/includes/framework.php';

$app = JFactory::getApplication('site');

if (!class_exists('VmConfig')) {
    require(JPATH_ADMINISTRATOR . '/components/com_virtuemart/helpers/config.php');
}
VmConfig::loadConfig();

if (!class_exists('VmModel')) {
    require(JPATH_ADMINISTRATOR . '/components/com_virtuemart/helpers/vmmodel.php');
}


$db = JFactory::getDbo();
$query = $db->getQuery(true)
    ->select('virtuemart_product_id')
    ->from($db->quoteName('#__virtuemart_products'));

$db->setQuery($query);
$products = $db->loadColumn();

$productModel = VmModel::getModel('product');
    
foreach ($products as $product_id) {
    $product = $productModel->getProduct((int)$product_id, true, true, true);
    $prices = $productModel->getPrice($product, [], 1);
    $finalPrice = isset($prices['salesPrice']) ? (float)$prices['salesPrice'] : 0;


    $queryUpdate = $db->getQuery(true)
        ->update($db->quoteName('#__virtuemart_product_prices'))
        ->set($db->quoteName('final_price') . ' = ' . $db->quote($finalPrice))
        ->where($db->quoteName('virtuemart_product_id') . ' = ' . (int)$product_id);
    
    $db->setQuery($queryUpdate);
    $db->execute();
}


[niosme]

Also thanks for your code 
I mean child products with category but wihtout price setted should inhertit the parent product price.Is there any workaround on that?

[hazael]

Hi,

This bug is quite logical. If a child product does not have a price, then this data cannot exist in the #__virtuemart_product_prices table. So we have to make a small change.
In the #__virtuemart_product_prices table, remove the 'final_price' column - it doesn't make sense for it to be there.

We need to add 'final_price' to the #__virtuemart_products column on an identical basis as before.

Code Select Expand

ALTER TABLE `#__virtuemart_products`
ADD COLUMN `final_price` decimal(15,5) NOT NULL DEFAULT 0 AFTER `product_mpn`;
In the file ( in 748 line), change the entire entry for case 'product_price':
administrator/components/com_virtuemart/models/product.php

Code Select Expand

case 'product_price':
$ff_select_price = ' , p.`final_price` AS product_price ';
$orderBy = ' ORDER BY p.`final_price` ' . $filterOrderDir . ', p.`virtuemart_product_id` ' . $filterOrderDir;
$joinPrice = FALSE;
break;


Now we are left with a minor modification to our file that activates updates to the final price for each published product. This means that if the sub-product does not have a price, it will copy that value from the parent product. Check it out, it should work

Code Select Expand

<?php
define('_JEXEC', 1);
require_once __DIR__ . '/includes/app.php';
$app = Joomla\CMS\Factory::getApplication('site');

if (!class_exists('VmConfig')) {
    require(JPATH_ADMINISTRATOR . '/components/com_virtuemart/helpers/config.php');
}
VmConfig::loadConfig();
if (!class_exists('VmModel')) {
    require(JPATH_ADMINISTRATOR . '/components/com_virtuemart/helpers/vmmodel.php');
}

$db = JFactory::getDbo();
$query = $db->getQuery(true)
    ->select('virtuemart_product_id, product_parent_id')
    ->from('#__virtuemart_products');

$db->setQuery($query);
$products = $db->loadObjectList();
$productModel = VmModel::getModel('product');

foreach ($products as $prodObj) {
    $product_id = (int) $prodObj->virtuemart_product_id;


    $product = $productModel->getProduct($product_id, true, true, true);
    if (!$product) {
        continue;
    }


    $prices = $productModel->getPrice($product, [], 1);
    $finalPrice = isset($prices['salesPrice']) ? (float)$prices['salesPrice'] : 0;


    if (
        $finalPrice == 0
        && !empty($prodObj->product_parent_id)
        && (int)$prodObj->product_parent_id > 0
    ) {
        $parentProduct = $productModel->getProduct($prodObj->product_parent_id, true, true, true);
        if ($parentProduct) {
            $pricesParent = $productModel->getPrice($parentProduct, [], 1);
            if (!empty($pricesParent['salesPrice'])) {
                $finalPrice = (float) $pricesParent['salesPrice'];
            }
        }
    }

    $queryUpdate = $db->getQuery(true)
        ->update('#__virtuemart_products')
        ->set('final_price = ' . $finalPrice)
        ->where('virtuemart_product_id = ' . $product_id);

    $db->setQuery($queryUpdate);
    $db->execute();
}

PS
The original price sorting in Virtuemart is a bit slower than the current one, because it required 2 tables. Now you have only one table. You can add an index to the 'final_price' column - this will make sorting even faster.

Code Select Expand

ALTER TABLE `#__virtuemart_products` ADD INDEX (`final_price`);
Just remember that after every price change ( for example, when you run discounts), it's a good idea to run this php code. Of course, it's best to add it to CRON and forget about these actions

[niosme]

I runned your file once to inform all the final_price once and the i putted on the end of the store function in product.php this to infrom the new prices on save and not with a cron. Thanks a lot.

In 2878 line, on the end of the store function:

// Update final price on store
$customProduct = $this->getProduct($data['virtuemart_product_id'], true, true, true);
$customPrices = $this->getPrice($customProduct, [], 1);
$customFinalPrice = isset($customPrices['salesPrice']) ? (float)$customPrices['salesPrice'] : 0;

if (
    $customFinalPrice == 0
    && !empty($customProduct->product_parent_id)
    && (int)$customProduct->product_parent_id > 0
) {
    $customParentProduct = $this->getProduct($customProduct->product_parent_id, true, true, true);
    if ($customParentProduct) {
        $customPricesParent = $this->getPrice($customParentProduct, [], 1);
        if (!empty($customPricesParent['salesPrice'])) {
            $customFinalPrice = (float) $customPricesParent['salesPrice'];
        }
    }
}

$db = JFactory::getDbo();
$queryUpdate = $db->getQuery(true)
    ->update('#__virtuemart_products')
    ->set('final_price = ' . $customFinalPrice)
    ->where('virtuemart_product_id = ' . $data['virtuemart_product_id']);

$db->setQuery($queryUpdate);
$db->execute();
// End of Update final price on store

[hazael]

Ok, just don't add this code to any php file that is run publicly on your site, because every time you run the site, it will unnecessarily refresh those prices in the database - with a lot of traffic and a lot of products, you may crash your server

[niosme]

Thanks a lot Yes just on store of the product to be done while we update or save the product!