How can I do a clean modified query in searchform for custom field

[mmv]

Edit : I mean, this post should be in the Developers Forum

Hi everyone,

In sugar 4.5.1d, I tried to have a improved and custom query in the search form with multi-select field in a multi-valued custom field :

e.g in a multiple selection field if I select cat and dog I want all the records who have :
custom_field_c =
cat
OR dog
OR cat,dog
So I use a like query : LIKE %dog% or %cat%

I don't find the right spot to do a clean code and clean modifications, but I find an (UGLY) way to do it :
I modified the include/SearchForm/SearchForm.php file near line 275.

I know this hack isn't clean, that's the reason for me to ask how to do it in better way as well.

Any Ideas ?

Thanks in advance!

Regards,

Michael MV.

Here is the cr*ppy code I made.

case 'in':
            if ($db_field == 'accounts_cstm.categories_c' )
                 {
                 if(is_array($parms['value']))
                               {
                                    $operator = 'in';
                                     $field_value = '';
                                    foreach($parms['value'] as $key => $val)
                                        {
                                        if($val != ' ' and $val != '')
                                                {
                                                if (!empty($field_value))
                                                         {
                                                        $field_value .= ' OR ';
                                                        }

                                                 $field_value .= "'%" . $GLOBALS['db']->quote($val) . "%'";
                                                 }
                                       }
                              }

                   $where .= "( ". $db_field . " LIKE ".$field_value." )"; 
                  }
                                else {
                                //HERE IS THE DEFAULT CODE
                                $where .=  $db_field . " in (".$field_value.')';
                                }
                                break;

[ivolator]

On your initial question:
See create_new_list_query() function. Review it carefully and Override it in your bean.
Also look in your modules/YOURMODULE/ListVeiw.php.
At the end of the file there is a call to $lv->setup(...); modify the where clause. But if you do that this where clause gets picked up by the function I just mentioned so you can just do it there.

Just as a note.

So I use a like query : LIKE %dog% or %cat%

This query can bog down your SQL server easy. Imagine SELECT * FROM xyz WHERE %a% or %e%;
Quite a lot of words would be in the result .... You might want to set some minimum length for the characters between the %s, but still %xyz% is not a good idea usually.