Author Topic: [FIXED - VM203H]: products ordering in the category with "Product Creation Date"  (Read 3844 times)


  • 3rd party VirtueMart Developer
  • Jr. Member
  • *
  • Posts: 105
    • Litera ltd
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
Code: [Select]
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'.
administrator/components/com_virtuemart/models/product.php - the function sortSearchListQuery():

Code: [Select]
// 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()';
case 'category_name':
$orderBy = ' ORDER BY `category_name` ';
$joinCategory = true ;
case 'category_description':
$orderBy = ' ORDER BY `category_description` ';
$joinCategory = true ;
case 'mf_name':
$orderBy = ' ORDER BY `mf_name` ';
$joinMf = true ;
case 'ordering':
$orderBy = ' ORDER BY `#__virtuemart_product_categories`.`ordering` ';
$joinCategory = true ;
case 'product_price':
//$filters[] = 'p.`virtuemart_product_id` = p.`virtuemart_product_id`';
$orderBy = ' ORDER BY `product_price` ';
$joinPrice = true ;
//<-- 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` ';
// LYV -->
default :
$orderBy = ' ORDER BY '.$this->_db->getEscaped($this->filter_order).' ';
} else {
$this->filter_order_Dir = '';

That's all


  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 10087
  • VM3.2 Cached and Optimized
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
Thank you. :-) 
I should fix your bug, please support the VirtueMart project and become a member
Extensions approved by the core team:


  • Jr. Member
  • **
  • Posts: 169
  • vm supporter member
  • VirtueMart Version: vm 3.0.x and jml 3.4.5

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


  • Beginner
  • *
  • Posts: 13
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..

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?


  • Beginner
  • *
  • Posts: 25

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:


The problem can be seen here:

The second option from the drop down on the left.

I guess is something to do with Cyrillic, but not sure what.

Please help !