Custom field search with keyword - Bug and solution

[BELGOR]

Hello
i use VM 2.0.20b but this problem is pretty much simillar to VM 2.0.22a...

When i search for products and use search in custom fields as well in same url like this:

Code Select Expand

index.php?option=com_virtuemart&view=category&keyword=KEYWORD&customfields[ID1]=KEYWORD&customfields[ID2]=KEYWORD

final SQL WHERE string is something like this

Code Select Expand

( `product_name` LIKE "%KEYWORD%" OR `p`.product_sku LIKE "%KEYWORD%" OR `product_s_desc` LIKE "%KEYWORD%" ) AND ( (pf.`virtuemart_custom_id`="ID1" and pf.`custom_value` like %KEYWORD%) OR (pf.`virtuemart_custom_id`="ID2" and pf.`custom_value` like %KEYWORD%) )
whitch means that KEYWORD have to be in product_name(desc,..) and in searched customfields at same time

i suggest this solution for next release:
in file administrator/components/com_virtuemart/models/product.php

change this part of code:

Code Select Expand

if (!empty($this->keyword) and $this->keyword !== '' and $group === FALSE) {

$keyword =  '"%' .str_replace(array(' ','-'),'%',$this->_db->getEscaped( $this->keyword, true )). '%"';
//$keyword = '"%' . $this->_db->getEscaped ($this->keyword, TRUE) . '%"';

foreach ($this->valid_search_fields as $searchField) {
if ($searchField == 'category_name' || $searchField == 'category_description') {
$joinCategory = TRUE;
}
else {
if ($searchField == 'mf_name') {
$joinMf = TRUE;
}
else {
if ($searchField == 'product_price') {
$joinPrice = TRUE;
}
else {
//vmdebug('sortSearchListQuery $searchField',$searchField);
/* if (strpos ($searchField, 'p.') == 1) {
$searchField = 'p`.`' . substr ($searchField, 2, (strlen ($searchField)));
//vmdebug('sortSearchListQuery $searchField recreated',$searchField);
}*/
}
}
}
if (strpos ($searchField, '`') !== FALSE){
$keywords_plural = preg_replace('/\s+/', '%" AND '.$searchField.' LIKE "%', $keyword);
$filter_search[] =  $searchField . ' LIKE ' . $keywords_plural;
} else {
$keywords_plural = preg_replace('/\s+/', '%" AND `'.$searchField.'` LIKE "%', $keyword);
$filter_search[] = '`'.$searchField.'` LIKE '.$keywords_plural;
//$filter_search[] = '`' . $searchField . '` LIKE ' . $keyword;
}
}
if (!empty($filter_search)) {
$where[] = '(' . implode (' OR ', $filter_search) . ')';
}
else {
$where[] = '`product_name` LIKE ' . $keyword;

//If they have no check boxes selected it will default to product name at least.
}
$joinLang = TRUE;
}

// vmdebug('my $this->searchcustoms ',$this->searchcustoms);
if (!empty($this->searchcustoms)) {
$joinCustom = TRUE;
foreach ($this->searchcustoms as $key => $searchcustom) {
$custom_search[] = '(pf.`virtuemart_custom_id`="' . (int)$key . '" and pf.`custom_value` like "%' . $this->_db->getEscaped ($searchcustom, TRUE) . '%")';
}
$where[] = " ( " . implode (' OR ', $custom_search) . " ) ";
}

to this:

Code Select Expand

if (!empty($this->keyword) and $this->keyword !== '' and $group === FALSE) {

$keyword =  '"%' .str_replace(array(' ','-'),'%',$this->_db->getEscaped( $this->keyword, true )). '%"';
//$keyword = '"%' . $this->_db->getEscaped ($this->keyword, TRUE) . '%"';

foreach ($this->valid_search_fields as $searchField) {
if ($searchField == 'category_name' || $searchField == 'category_description') {
$joinCategory = TRUE;
}
else {
if ($searchField == 'mf_name') {
$joinMf = TRUE;
}
else {
if ($searchField == 'product_price') {
$joinPrice = TRUE;
}
else {
//vmdebug('sortSearchListQuery $searchField',$searchField);
/* if (strpos ($searchField, 'p.') == 1) {
$searchField = 'p`.`' . substr ($searchField, 2, (strlen ($searchField)));
//vmdebug('sortSearchListQuery $searchField recreated',$searchField);
}*/
}
}
}
if (strpos ($searchField, '`') !== FALSE){
$keywords_plural = preg_replace('/\s+/', '%" AND '.$searchField.' LIKE "%', $keyword);
$filter_search[] =  $searchField . ' LIKE ' . $keywords_plural;
} else {
$keywords_plural = preg_replace('/\s+/', '%" AND `'.$searchField.'` LIKE "%', $keyword);
$filter_search[] = '`'.$searchField.'` LIKE '.$keywords_plural;
//$filter_search[] = '`' . $searchField . '` LIKE ' . $keyword;
}
}

if (!empty($this->searchcustoms)) {
if (empty($filter_search)) {
$filter_search[] = '`product_name` LIKE ' . $keyword;
}
$joinCustom = TRUE;
foreach ($this->searchcustoms as $key => $searchcustom) {
$searchcustom =  '"%' .str_replace(array(' ','-'),'%',$this->_db->getEscaped( $searchcustom, true )). '%"';
//$searchcustom = '"%' . $this->_db->getEscaped ($searchcustom, TRUE) . '%"';
$searchcustom_plural = preg_replace('/\s+/', '%" AND pf.`custom_value` LIKE "%', $searchcustom);
$filter_search[] = '(pf.`virtuemart_custom_id`="' . (int)$key . '" AND pf.`custom_value` LIKE ' . $searchcustom_plural . ')';
}
}

if (!empty($filter_search)) {
$where[] = '(' . implode (' OR ', $filter_search) . ')';
}
else {
$where[] = '`product_name` LIKE ' . $keyword;

//If they have no check boxes selected it will default to product name at least.
}
$joinLang = TRUE;
}

// vmdebug('my $this->searchcustoms ',$this->searchcustoms);
if (!empty($this->searchcustoms) && (empty($this->keyword) || $this->keyword === '' || $group !== FALSE)) {
$joinCustom = TRUE;
foreach ($this->searchcustoms as $key => $searchcustom) {
$custom_search[] = '(pf.`virtuemart_custom_id`="' . (int)$key . '" and pf.`custom_value` like "%' . $this->_db->getEscaped ($searchcustom, TRUE) . '%")';
}
$where[] = " ( " . implode (' OR ', $custom_search) . " ) ";
}