3.0.19.6 Problem in searching categories in administrator

[Freddo]

An error has occurred.
1052 Column 'category_name' in where clause is ambiguous SQL=SELECT c.`virtuemart_category_id`, c.`ordering`, c.`published`, cx.`category_child_id`, cx.`category_parent_id`, c.`shared` , IFNULL(l.category_description,ld.category_description) as category_description, IFNULL(l.category_name,ld.category_name) as category_name FROM `#__virtuemart_categories` as c LEFT JOIN `#__virtuemart_categories_el_gr` as ld ON ld.`virtuemart_category_id` = c.`virtuemart_category_id` LEFT JOIN `#__virtuemart_categories_en_gb` as l ON l.`virtuemart_category_id` = c.`virtuemart_category_id` LEFT JOIN `#__virtuemart_category_categories` AS cx ON c.`virtuemart_category_id` = cx.`category_child_id` WHERE ( category_name LIKE "%access%" OR category_description LIKE "%access%") GROUP BY virtuemart_category_id ORDER BY category_name DESC

[jenkinhill]

I am unable to duplicate that. Is this a new installation with sample data - or what?

[Studio 42]

@Jenkinhill : this can only be on having multi language shop else you have only 1 category_name.
The query should be WHERE (l .category_name LIKE "%access%" OR l .category_description LIKE "%access%")
so the Column 'category_name' in where clause is not ambiguous anymore
But in this case, you cannot search for missing translation key.
So it should be (l .category_name LIKE "%access%" OR l .category_description LIKE "%access%" OR  l .category_name LIKE "%access%" OR l .category_description LIKE "%access%")

[Milbo]

Patrick, the sql fallback is much more complex
IFNULL(l.category_description,ld.category_description) as category_description, IFNULL(l.category_name,ld.category_name) as category_name

so I do not understand the problem with this query. IFNULL(l.category_name,ld.category_name) as category_name prevents this error, because there is only one category_name, then.

I try the query in the db direct, lets see

[Milbo]

I am unable to duplicate that. Is this a new installation with sample data - or what?

You need two languages and you must search in your non default language.

[Studio 42]

I'm not sure but
IFNULL(l.category_name,ld.category_name) as category_name
only set 1 category_name for the result, but WHEN search in the 2 tables and need the table prefix in this case.
I had already similar problem, it's why i mean it comes from here.

[Freddo]

also another in other site same vm version

An error has occurred.
1052 Column 'product_name' in order clause is ambiguous SQL=SELECT SQL_CALC_FOUND_ROWS p.`virtuemart_product_id` FROM `#__virtuemart_products` as p LEFT JOIN `#__virtuemart_products_el_gr` as ld ON ld.`virtuemart_product_id` = p.`virtuemart_product_id` LEFT JOIN `#__virtuemart_products_en_gb` as l ON l.`virtuemart_product_id` = p.`virtuemart_product_id` LEFT JOIN `#__virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id` WHERE ( p.`virtuemart_vendor_id` = "1" ) group by p.`virtuemart_product_id` ORDER BY `pc`.`ordering` DESC, `product_name` DESC LIMIT 0, 30

I had enabled 1 lang and then I added second. When I click on the products on administrator I get this error...

[Freddo]

in first case the problem is in where

WHERE ( category_name LIKE "%access%" OR category_description LIKE "%access%")

needs the same treetment as select
administrator/components/com_virtuemart/models/category.php
line 375

I tried this:

         if(VmConfig::$defaultLang!=VmConfig::$vmlang and VmConfig::$langCount>1){
            $where[] = ' ( l.category_name LIKE '.$keyword.'
                  OR l.category_description LIKE '.$keyword.') OR 
                  ( ld.category_name LIKE '.$keyword.'
                  OR ld.category_description LIKE '.$keyword.')';
         } else {
            $where[] = ' ( category_name LIKE '.$keyword.'
                  OR category_description LIKE '.$keyword.') ';
         }

[Freddo]

the second gave me results when I commented out line 518 on administrator/components/com_virtuemart/models/product.php line 518
            $orderBy = ' ORDER BY `pc`.`ordering` '.$filterOrderDir.', `product_name` '.$filterOrderDir;

[Milbo]

This function should work

Code Select Expand


public function getCategories($onlyPublished = true, $parentId = false, $childId = false, $keyword = "", $vendorId = false) {

static $cats = array();
VmModel::setDebugSql(true);
$select = ' c.`virtuemart_category_id`, c.`ordering`, c.`published`, cx.`category_child_id`, cx.`category_parent_id`, c.`shared` ';

$joins = ' FROM `#__virtuemart_categories` as c ';

$where = array();

if( $onlyPublished ) {
$where[] = " c.`published` = 1 ";
}
if( $parentId !== false ){
$where[] = ' cx.`category_parent_id` = '. (int)$parentId;
}

if( $childId !== false ){
$where[] = ' cx.`category_child_id` = '. (int)$childId;
}

if($vendorId===false){
$vendorId = vmAccess::isSuperVendor();
}

if($vendorId!=1){

$where[] = ' (c.`virtuemart_vendor_id` = "'. (int)$vendorId. '" OR c.`shared` = "1") ';
}

$langFields = array('category_description','category_name');

$langFback = ( !VmConfig::get('prodOnlyWLang',false) and VmConfig::$defaultLang!=VmConfig::$vmlang and VmConfig::$langCount>1 );

if($langFback){


$useJLback = false;
if(VmConfig::$defaultLang!=VmConfig::$jDefLang){
$joins .= ' LEFT JOIN `#__virtuemart_categories_'.VmConfig::$jDefLang.'` as ljd ON ljd.`virtuemart_category_id` = c.`virtuemart_paymentmethod_id`';
$useJLback = true;
}
foreach($langFields as $langField){
$expr2 = 'ld.'.$langField;
if($useJLback){
$expr2 = 'IFNULL(ld.'.$langField.',ljd.'.$langField.')';
}
$select .= ', IFNULL(l.'.$langField.','.$expr2.') as '.$langField.'';
}
$joins .= ' LEFT JOIN `#__virtuemart_categories_'.VmConfig::$defaultLang.'` as ld ON ld.`virtuemart_category_id` = c.`virtuemart_category_id`';
$joins .= ' LEFT JOIN `#__virtuemart_categories_'.VmConfig::$vmlang.'` as l ON l.`virtuemart_category_id` = c.`virtuemart_category_id`';
} else {
$select .= ', category_description, category_name';
$joins .= ' LEFT JOIN `#__virtuemart_categories_'.VmConfig::$vmlang.'` as l ON l.`virtuemart_category_id` = c.`virtuemart_category_id` ';
}

$joins .= ' LEFT JOIN `#__virtuemart_category_categories` AS cx ON c.`virtuemart_category_id` = cx.`category_child_id`';


$whereOr = array();
if( !empty( $keyword ) ) {
$db = JFactory::getDBO();
$keyword = '"%' . $db->escape( $keyword, true ) . '%"' ;
foreach ($langFields as $langField) {
$whereOr[] =  '`l`.'.$langField . ' LIKE ' . $keyword;
if($langFback){
$whereOr[] =  '`ld`.'.$langField . ' LIKE ' . $keyword;
if(VmConfig::$defaultLang!=VmConfig::$jDefLang){
$whereOr[] =  '`ljd`.'.$langField . ' LIKE ' . $keyword;
}
}
}
}

$whereString = '';
if (count($where) > 0 or count($whereOr)){
$whereString = ' WHERE ';//.implode(' AND ', $where) .' AND ('.implode(' OR ', $where).')' ;
if (count($where) > 0){
$whereString .= implode(' AND ', $where);
if (count($whereOr) > 0){
$whereString .= ' AND ';
}
}
if (count($whereOr) > 0){
$whereString .= '('.implode(' OR ', $whereOr).')';
}
} else {
$whereString = 'WHERE 1 ';
}

if(trim($this->_selectedOrdering) == 'c.ordering'){
$this->_selectedOrdering = 'c.ordering, category_name';
}
$ordering = $this->_getOrdering();

$hash = md5($keyword.'.'.(int)$parentId.VmConfig::$vmlang.(int)$childId.$this->_selectedOrderingDir.(int)$vendorId.$this->_selectedOrdering);
if(!isset($cats[$hash])){
$cats[$hash] = $this->_category_tree = $this->exeSortSearchListQuery(0,$select,$joins,$whereString,'GROUP BY virtuemart_category_id',$ordering );
}

return $cats[$hash];

}