[fixed] Mysql Error Sorting Order 2.0.4

[teobgeno]

Mysql throws an error when i try to sort products by categories in admin panel  (joomla 1.5.26 Vm 2.0.4)

vmError: exeSortSearchListQuery Column 'virtuemart_product_id' in order clause is ambiguous SQL=SELECT SQL_CALC_FOUND_ROWS * FROM `jos_virtuemart_products_el_gr` as l JOIN `jos_virtuemart_products` AS p using (`virtuemart_product_id`) LEFT JOIN `jos_virtuemart_product_categories` ON p.`virtuemart_product_id` = `jos_virtuemart_product_categories`.`virtuemart_product_id` LEFT JOIN `jos_virtuemart_categories_el_gr` as c ON c.`virtuemart_category_id` = `jos_virtuemart_product_categories`.`virtuemart_category_id` WHERE ( `jos_virtuemart_product_categories`.`virtuemart_category_id` = 1) group by p.`virtuemart_product_id` ORDER BY virtuemart_product_id ASC LIMIT 0, 10

at administrator/components/com_virtuemart/models/product.php line 135

Code Select Expand


$filter_order = strtolower($app->getUserStateFromRequest( 'com_virtuemart.'.$view.'.filter_order', 'filter_order',$this->_selectedOrdering , 'cmd' ));


$filter_order has a value of virtuemart_product_id when it should be p.virtuemart_product_id

the default $filter_order at VirtueMartModelProduct class is from default p.virtuemart_product_id and would be worked if there was not an assign at line 143

Code Select Expand


$this->filter_order = $filter_order;

I used a temporary solution with an if  before the   ($this->filter_order = $filter_order;) assign

Code Select Expand


if($filter_order=='virtuemart_product_id')
{
$filter_order='p.virtuemart_product_id';
}

I know it has to do also with mysql settings so this error may not appear to all cases.

[simbus82]

Mysql throws an error when i try to sort products by categories in admin panel  (joomla 1.5.26 Vm 2.0.4)

vmError: exeSortSearchListQuery Column 'virtuemart_product_id' in order clause is ambiguous SQL=SELECT SQL_CALC_FOUND_ROWS * FROM `jos_virtuemart_products_el_gr` as l JOIN `jos_virtuemart_products` AS p using (`virtuemart_product_id`) LEFT JOIN `jos_virtuemart_product_categories` ON p.`virtuemart_product_id` = `jos_virtuemart_product_categories`.`virtuemart_product_id` LEFT JOIN `jos_virtuemart_categories_el_gr` as c ON c.`virtuemart_category_id` = `jos_virtuemart_product_categories`.`virtuemart_category_id` WHERE ( `jos_virtuemart_product_categories`.`virtuemart_category_id` = 1) group by p.`virtuemart_product_id` ORDER BY virtuemart_product_id ASC LIMIT 0, 10

at administrator/components/com_virtuemart/models/product.php line 135

Code Select Expand


$filter_order = strtolower($app->getUserStateFromRequest( 'com_virtuemart.'.$view.'.filter_order', 'filter_order',$this->_selectedOrdering , 'cmd' ));


$filter_order has a value of virtuemart_product_id when it should be p.virtuemart_product_id

the default $filter_order at VirtueMartModelProduct class is from default p.virtuemart_product_id and would be worked if there was not an assign at line 143

Code Select Expand


$this->filter_order = $filter_order;

I used a temporary solution with an if  before the   ($this->filter_order = $filter_order;) assign

Code Select Expand


if($filter_order=='virtuemart_product_id')
{
$filter_order='p.virtuemart_product_id';
}

I know it has to do also with mysql settings so this error may not appear to all cases.

I have a similar error when i do a search with VM Product Search module!!!!!

But your code don't resolve my problem...

[teobgeno]

Maybe you put the if statement after the assignment. I provide you the whole block of code

Code Select Expand



//Filter order and dir  This is unecessary complex and maybe even wrong, but atm it seems to work
if($app->isSite()){
$filter_order = JRequest::getString('orderby', VmConfig::get('browse_orderby_field','p.virtuemart_product_id'));
$filter_order     = $this->checkFilterOrder($filter_order);

$filter_order_Dir = strtoupper(JRequest::getWord('order', 'ASC'));
$valid_search_fields = VmConfig::get('browse_search_fields');
} else {
$filter_order = strtolower($app->getUserStateFromRequest( 'com_virtuemart.'.$view.'.filter_order', 'filter_order',$this->_selectedOrdering , 'cmd' ));

$filter_order     = $this->checkFilterOrder($filter_order);
$filter_order_Dir = strtoupper($app->getUserStateFromRequest( $option.'.'.$view.'.filter_order_Dir', 'filter_order_Dir', '', 'word' ));
$valid_search_fields = array('product_name');
//fix
if($filter_order=='virtuemart_product_id')
    {
      $filter_order='p.virtuemart_product_id';
    }
//fix

}

$filter_order_Dir = $this->checkFilterDir($filter_order_Dir);
[b]$this->filter_order = $filter_order;[/b]
$this->filter_order_Dir = $filter_order_Dir;
$this->valid_search_fields = $valid_search_fields;



Look where is the if and i bolded the assignment too.
Of couse your error maybe triggered by onother query  so in that case post the error that VM throws and the   http url you browse when the error occured.

[simbus82]

Of couse your error maybe triggered by onother query  so in that case post the error that VM throws and the   http url you browse when the error occured.

Thank, maybe my error is triggerd by other errors..

I see that you are a beast in bug searching

My problem is here if you want take a look!

http://forum.virtuemart.net/index.php?topic=101053.msg335015#msg335015

[Milbo]

But the fix is completly wrong, teobgeno.

The real answer is to change  removevalidOrderingFieldName to

Code Select Expand


function removevalidOrderingFieldName($name){
$key=array_search($name, $this->_validOrderingFieldName);
unset($this->_validOrderingFieldName[$key]) ;
}


[teobgeno]

Thank you milbo for your reply . Your fix works fine.
The modifications i made on my code is for sure a temporary solution until you release a fix for the bugs.My intesion is to share the problems i have ,with others virtuemart users and probably help with my programming experience as much as i can or also to be helped by others.For sure i don't know the programming structure of virtuemart as you and your team know it so your solution will be much better than mine. The code i present as solution is as i said is a temporary solution and the user that will use it has for sure the brain to test it and not completely rely on it. These codes modification is actually your obligation and not mine.

The fix i made works for me fine and with no conflicts in other sections so i would like if it is possible to explain me programmaticaly why the fix is completly wrong

simbus82 yoiur turn

I tested the query you provide at your post and you are correct too much single quotation marks

at administrator/components/com_virtuemart/models/product.php

search for
$filter_search[] = '`'.$searchField.'` LIKE '.$keyword;

modify it to
$filter_search[] = '`'.$searchField.' LIKE '.$keyword;

and make  a test see if it is ok . Be cautious because there maybe conflicts elesewhere if it finally works.

[Milbo]

Teobgeno, hey no problem. Of course you do right.

Your fix is from your point of view right, because it changes only this file and so you have no side effects. I just wondered why the ordering field is still available in the model. The real reason that I said it is completly wrong was just

Code Select Expand


[b]$this->filter_order = $filter_order;[/b]

Look where is the if and i bolded the assignment too.
Of couse your error maybe triggered by onother query  so in that case post the error that VM throws and the   http url you browse when the error occured.

Your bold broke the code :-)

[simbus82]

I tested the query you provide at your post and you are correct too much single quotation marks

at administrator/components/com_virtuemart/models/product.php
search for
$filter_search[] = '`'.$searchField.'` LIKE '.$keyword;
modify it to
$filter_search[] = '`'.$searchField.' LIKE '.$keyword;

Thanks for the help, but the problem is not resolved. PS: write in my thread otherwise we ruin your thread about the BE problem! ;-)

Your bold broke the code :-)

Obviously I have not entered the bold tag ehhehe, but now i think this code is not so much related to my search module problem (I thought it was an error of ordering also in frontend)

[Milbo]

Please look here http://forum.virtuemart.net/index.php?topic=101053.0