[FIXED - VM203H]: products ordering in the category with "Product Creation Date"

[lysov]

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

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'.
Solution:
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()';
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

[Milbo]

Thank you. :-) 

[slammy]

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

[harikjr]

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?

[ovcharoff]

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 !