Hello,
When I set ordering of the products in category to option "creation date" products are disappear and I'm a little bit confused about its current behaviour.
If you switch VM to debug mode you can see following MySQL error: #1052 - Column 'created_on' in order clause is ambiguous
SELECT SQL_CALC_FOUND_ROWS * FROM `#__virtuemart_products_ru_ru` AS l
JOIN `#__virtuemart_products` AS p
USING ( `virtuemart_product_id` )
LEFT JOIN `#__virtuemart_product_categories` ON p.`virtuemart_product_id` = `#__virtuemart_product_categories`.`virtuemart_product_id`
LEFT JOIN `#__virtuemart_categories_ru_ru` AS c ON c.`virtuemart_category_id` = `#__virtuemart_product_categories`.`virtuemart_category_id`
LEFT JOIN `#__virtuemart_product_shoppergroups` ON p.`virtuemart_product_id` = `#__virtuemart_product_shoppergroups`.`virtuemart_product_id`
LEFT OUTER JOIN `#__virtuemart_shoppergroups` AS s ON s.`virtuemart_shoppergroup_id` = `#__virtuemart_product_shoppergroups`.`virtuemart_shoppergroup_id`
WHERE (p.`published` = "1" AND `#__virtuemart_product_categories`.`virtuemart_category_id` =12
AND (s.`virtuemart_shoppergroup_id` = "2" OR (s.`virtuemart_shoppergroup_id`) IS NULL))
GROUP BY p.`virtuemart_product_id`
ORDER BY created_on
To avoid this situation, we should use the expression 'ORDER BY p.`created_on`' instead 'ORDER BY created_on'.
Solution:
administrator/components/com_virtuemart/models/product.php - the function sortSearchListQuery():
// special orders case
switch ($this->filter_order) {
case 'product_special':
$where[] = ' p.`product_special`="1" ';// TODO Change to a individual button
$orderBy = 'ORDER BY RAND()';
break;
case 'category_name':
$orderBy = ' ORDER BY `category_name` ';
$joinCategory = true ;
break;
case 'category_description':
$orderBy = ' ORDER BY `category_description` ';
$joinCategory = true ;
break;
case 'mf_name':
$orderBy = ' ORDER BY `mf_name` ';
$joinMf = true ;
break;
case 'ordering':
$orderBy = ' ORDER BY `#__virtuemart_product_categories`.`ordering` ';
$joinCategory = true ;
break;
case 'product_price':
//$filters[] = 'p.`virtuemart_product_id` = p.`virtuemart_product_id`';
$orderBy = ' ORDER BY `product_price` ';
$joinPrice = true ;
break;
//<-- LYV: TO AVOID MySQL error #1052 (Column 'created_on' in order clause is ambiguous) when order is set to Creation Date
case 'created_on':
$orderBy = ' ORDER BY p.`created_on` ';
break;
// LYV -->
default :
if(!empty($this->filter_order)){
$orderBy = ' ORDER BY '.$this->_db->getEscaped($this->filter_order).' ';
} else {
$this->filter_order_Dir = '';
}
break;
}
That's all
Thank you. :-)
Hi,
after migration from vm 1.1.x to vm2.0.3_g and Joomla 1.5.9 to 2.5.1 I am hiking for some last thing to correct. Beside guys, thx for your great work on vm2. I am still impressed.
Thx for the last tipp, this is excellent. After editing case: created_on in product.php I get a default ordering by creation_date of the product -which is fine - but it´s in the wrong order for this specific field in normal use. It´s ascending, the oldest products I created on vm 1.1.x show up first, the latest I added show up last in the category overviews. The switching link between ASC and DESC works fine, but I don´t want my clients to click first for the right direction.
I found some forum entries where ppl talk about this issue but in another context, making changes to /administrator/components/com_virtuemart/models/product.php. I figured out that line 132 at $filter_order_Dir = strtoupper(JRequest::getWord('order', 'ASC')); seems to be the default order setting. if I switch this to DESC the overview direction is correct, but then the switching link does not function any longer.
Does anyone have an idea what I have else to change or will there be a backend-possibilty to set up direction in the category settings?
best regards
jens
i dont' know if this is 100% related..
my ordering was perfect.. except i added some ordering options and when switching to them... i got an issue where only 1-5 were shown in the front end. .. even when i specified. to show 16 on the store category page.. it'd stills how 5.. 4 in one row... and 1 on the second row..
http://aleksandrshevchenko.org/store/categories/sermons
default per row is set to 4.
why is this..? i tried changing the settings.. nothing..
changing default to row 3 will put 3 in row one and 2 in row 2.. interesting.. any one know why?
Hello.
I'm using latest version of Joomla 2.5 and VM2. My default language is Bulgarian, therefore I've Cyrillic SEF's and so one.
The problem is as follows: When I enable the sort by "create date" and try sorting in the front end, I would get a 440 error with the following url:
httt://mywebsite.com/myvmcategory/от,`p`.created_on
The problem can be seen here:
http://web-creativity.net/phytolek.com/%D1%87%D0%B0%D0%B9%D0%BE%D0%B2%D0%B5
The second option from the drop down on the left.
I guess is something to do with Cyrillic, but not sure what.
Please help !