News:

Support the VirtueMart project and become a member

Main Menu

orderby=p.modified_on doesn't work

Started by Florentp, April 25, 2013, 15:10:06 PM

Previous topic - Next topic

Florentp

Hi.

I did the update form 2.0.16 to 2.0.20b (problem also occure on 2.0.20c) and suddenly, a link on my menu stopped to work.
This link was like that : index.php?option=com_virtuemart&Itemid=2467&lang=fr&limit=27&limitstart=0&order=DESC&orderby=modified_on&view=category&virtuemart_category_id=179

And here is the error returned on front-end :
vmError: exeSortSearchListQuery Column 'modified_on' in order clause is ambiguous SQL=SELECT SQL_CALC_FOUND_ROWS l.`virtuemart_product_id` FROM `j17_virtuemart_products_fr_fr` as l JOIN `j17_virtuemart_products` AS p using (`virtuemart_product_id`) LEFT JOIN `j17_virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id` LEFT JOIN `j17_virtuemart_categories_fr_fr` as c ON c.`virtuemart_category_id` = `pc`.`virtuemart_category_id` LEFT JOIN `j17_virtuemart_product_shoppergroups` ON p.`virtuemart_product_id` = `j17_virtuemart_product_shoppergroups`.`virtuemart_product_id` LEFT OUTER JOIN `j17_virtuemart_shoppergroups` as s ON s.`virtuemart_shoppergroup_id` = `j17_virtuemart_product_shoppergroups`.`virtuemart_shoppergroup_id` WHERE ( p.`published`="1" AND p.`product_in_stock` - p.`product_ordered` >"0" AND `pc`.`virtuemart_category_id` = 179 AND `pc`.`virtuemart_category_id` > 0 AND ( s.`virtuemart_shoppergroup_id`= "2" OR s.`virtuemart_shoppergroup_id` IS NULL ) ) group by p.`virtuemart_product_id` ORDER BY modified_on DESC LIMIT 0, 27

Alatak told me to use "p.modified.on" instead of "modified_on" in my link. So my link is now build like that :
index.php?option=com_virtuemart&Itemid=2467&lang=fr&limit=27&limitstart=0&order=DESC&orderby=p.modified_on&view=category&virtuemart_category_id=179

Well, now, it works ! Product are returned, seems fine. BUT, they are not well ordered !
I activate the Debug Mode, and i Got this message :
Quotevmdebug checkValidOrderingField:VirtueMartModelProduct programmer choosed invalid ordering p.modified_on, use `p`.created_on

This mean that virtuemart think that p.modified_on isn't correct, and decide to use `p`.created_on instead...
Really strange, because when i do the correct SQL request by myself on phpmyadmin, products ids are well returned, and well orderder...
SELECT SQL_CALC_FOUND_ROWS l.`virtuemart_product_id` FROM `j17_virtuemart_products_fr_fr` as l JOIN `j17_virtuemart_products` AS p using (`virtuemart_product_id`) LEFT JOIN `j17_virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id` LEFT JOIN `j17_virtuemart_categories_fr_fr` as c ON c.`virtuemart_category_id` = `pc`.`virtuemart_category_id` LEFT JOIN `j17_virtuemart_product_shoppergroups` ON p.`virtuemart_product_id` = `j17_virtuemart_product_shoppergroups`.`virtuemart_product_id` LEFT OUTER JOIN `j17_virtuemart_shoppergroups` as s ON s.`virtuemart_shoppergroup_id` = `j17_virtuemart_product_shoppergroups`.`virtuemart_shoppergroup_id` WHERE ( p.`published`="1" AND p.`product_in_stock` - p.`product_ordered` >"0" AND `pc`.`virtuemart_category_id` = 179 AND `pc`.`virtuemart_category_id` > 0 AND ( s.`virtuemart_shoppergroup_id`= "2" OR s.`virtuemart_shoppergroup_id` IS NULL ) ) group by p.`virtuemart_product_id` [b]ORDER BY p.modified_on[/b] DESC LIMIT 0, 27

So there is definitly something wrong with that parameter.
Web programmer. Passionate.
PHP, SQL, Javascript, JQuery, css.
Skype : florent.indienboutique

franzpeter

I think they just did forget to add a case instruction to order by modified_on in the code or maybe this ordering method should not be available and they did forget to delete that filter method.

Florentp

Quote from: franzpeter on April 25, 2013, 16:40:41 PM
I think they just did forget to add a case instruction to order by modified_on in the code or maybe this ordering method should not be available and they did forget to delete that filter method.

But it was working in previous release :/
(modified_on)
Web programmer. Passionate.
PHP, SQL, Javascript, JQuery, css.
Skype : florent.indienboutique

Florentp

Could someone clarify that problem, and tell us if this will be fixed in the next release ?

Thanks  :)
Web programmer. Passionate.
PHP, SQL, Javascript, JQuery, css.
Skype : florent.indienboutique

Milbo

Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

Florentp

Quote from: Milbo on May 02, 2013, 12:45:10 PM
Did you stored your config again?


Hum, what do you mean ?
I did the update to VM 2.0.20C with the liveupdate function, same for AIO, and did the "Tables update".
Forgot something ?
Web programmer. Passionate.
PHP, SQL, Javascript, JQuery, css.
Skype : florent.indienboutique

Florentp

Hey Milbo  8)
Could you just give me more precision ?
You mean keep the configuration parameter file before update and then upload it after the update ?
I never did that, because i never had any problem with the configuration, actually everything seems fine.
But i can check if there is any change between the actual configuration file, and the old one, before the update (I have a backup).

Thanks
Web programmer. Passionate.
PHP, SQL, Javascript, JQuery, css.
Skype : florent.indienboutique

Florentp

Hello,
Please, i nead to fix this :/

Thanks !
Web programmer. Passionate.
PHP, SQL, Javascript, JQuery, css.
Skype : florent.indienboutique

Milbo

Did you tried the last version? I mean the 2.0.21f?
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

Florentp

#9
Hi Milbo,
I'm actually trying 2.0.21H, and this is now working :D
Web programmer. Passionate.
PHP, SQL, Javascript, JQuery, css.
Skype : florent.indienboutique