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
I am unable to duplicate that. Is this a new installation with sample data - or what?
@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%")
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
Quote from: jenkinhill on February 14, 2017, 15:11:27 PM
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.
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.
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...
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.') ';
}
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;
This function should work
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];
}