Author Topic: Search Profiles: Search custom field with LIKE operator instead of = (Read 3198 times)

areaten · November 23, 2009, 05:06:30 pm

Hi
I have a custom profile as a search, and want to search a custom text field, but when it is exposed in the search profile the operator is "=" and I want it to be "LIKE" so that it doesn't just provide exact matches. Much like the contact name field which has a LIKE operator. Is there a way to do this? Or somewhere in the code?
Thanks for any suggestions

Donald Lobo · November 23, 2009, 07:13:09 pm

you'll need to modify the code here:

CRM/Core/BAO/CustomQuery.php

lobo

areaten · November 24, 2009, 02:12:29 pm

Thanks lobo.

I don't know php so I'm at a loss with this. I've had a look and I'm guessing
case 'String':
on line 311?

Testing my search profile again, it seems that by adding the %term% wildcards turns the search operator into LIKE and gives me the desired results, so really I guess what I'm after is the custom field search to automatically have wildcards added to the value entered..

mylo1882 · December 14, 2009, 04:34:31 am

Could anyone point out what code needs changing to achieve the above? Custom data - Alphanumeric - Text. I would like for the search to insert % wherever there is a space and at the begining and end of the search.
e.g. 'please search this' would search for '%please%search%this%'

Code: [Select]

                case 'String':
                    $sql = "LOWER($fieldName)";
                    // if we are coming in from listings,
                    // for checkboxes the value is already in the right format and is NOT an array 
                    if ( is_array( $value ) ) {
                        require_once 'CRM/Core/BAO/CustomOption.php';

                        //ignoring $op value for checkbox and multi select
                        $sqlValue = array( );
                        if ($field['html_type'] == 'CheckBox') {
                            foreach ( $value as $k => $v ) { 
                                $sqlValue[] = "( $sql like '%" . CRM_Core_BAO_CustomOption::VALUE_SEPERATOR . $k . CRM_Core_BAO_CustomOption::VALUE_SEPERATOR . "%' ) ";
                            }
                            $this->_where[$grouping][] = implode( ' AND ', $sqlValue );
                            $this->_qill[$grouping][]  = "$field[label] $op $qillValue";
                        } else { // for multi select
                            foreach ( $value as $k => $v ) { 
                                $sqlValue[] = "( $sql like '%" . CRM_Core_BAO_CustomOption::VALUE_SEPERATOR . $v . CRM_Core_BAO_CustomOption::VALUE_SEPERATOR . "%' ) ";
                            }
                            $this->_where[$grouping][] = implode( ' AND ', $sqlValue ); 
                            $this->_qill[$grouping][]  = "$field[label] $op $qillValue";
                        }                    
                    } else {
                        if ( $field['is_search_range'] && is_array( $value ) ) {
                            $this->searchRange( $field['id'],
                                                $field['label'],
                                                $field['data_type'],
                                                $fieldName,
                                                $value,
                                                $grouping );
                        } else {
                            $val = CRM_Utils_Type::escape( strtolower(trim($value)), 'String' );
                            if ( $wildcard ) {
                                $val = strtolower( addslashes( $val ) );
                                $val = "%$val%";
                                $op  = 'LIKE';
                            }
                            $this->_where[$grouping][] = "{$sql} {$op} '{$val}'";
                            $this->_qill[$grouping][]  = "$field[label] $op $qillValue";
                        }
                    } 
                    continue;

Thank you

Donald Lobo · December 14, 2009, 06:47:28 am

the code at the bottom of the snippet, i.e. the last else clause is where u'll need to make the changes

lobo

mylo1882 · December 15, 2009, 02:54:14 am

Thanks lobo,

Have been playing around with this. But I think it is much more complicated than my very limited PHP skills

. If anyone has any suggestions it would be most appreciated.

Donald Lobo · December 15, 2009, 08:34:41 am

you might want to consider hiring someone from http://civicrm.org/professional/

lobo

mylo1882 · December 16, 2009, 01:58:56 am

Ok will do. Our charity has a very limited budget so I was hoping this was maybe something simple for those who know PHP.

I can see from looking into it its not that simple.

Thanks

Mylo

Chris7789 · March 01, 2010, 05:22:03 am

Got there in the end. Code below will make the necessary changes in 2 different ways.

Code: [Select]

$val = strtolower( addslashes( $val ) );
 
// works but would replace sequential spaces with multiple %
$val = str_replace( ' ', '%', $val );
 
// replaces sequential spaces with only one %
$val = preg_replace('/[ ]+/', '%', $val);
 
$val = "%$val%";

CiviCRM Community Forums (archive)

News:

Author Topic: Search Profiles: Search custom field with LIKE operator instead of = (Read 3198 times)

areaten

Search Profiles: Search custom field with LIKE operator instead of =

Donald Lobo

Re: Search Profiles: Search custom field with LIKE operator instead of =

areaten

Re: Search Profiles: Search custom field with LIKE operator instead of =

mylo1882

Re: Search Profiles: Search custom field with LIKE operator instead of =

Donald Lobo

Re: Search Profiles: Search custom field with LIKE operator instead of =

mylo1882

Re: Search Profiles: Search custom field with LIKE operator instead of =

Donald Lobo

Re: Search Profiles: Search custom field with LIKE operator instead of =

mylo1882

Re: Search Profiles: Search custom field with LIKE operator instead of =

Chris7789

Re: Search Profiles: Search custom field with LIKE operator instead of =