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
administrator/components/com_virtuemart/models/product.php
Maybe in 748
case 'product_price':
(...)
break;
change to
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.
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:
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
case 'product_price':
(...)
break;
to:
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:
<?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
prices.png
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:
<?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();
}
Also thanks for your code 8)
I mean child products with category but wihtout price setted should inhertit the parent product price.Is there any workaround on that?
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.
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
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 :)
<?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.
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 ;)
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
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 :D
Thanks a lot :) Yes just on store of the product to be done while we update or save the product!