Author Topic: Profile Search Form -- Implicit wildcards missing from custom fields (Read 2385 times)

lcdweb · December 28, 2007, 08:09:40 am

If you build a profile form and expose to the frontend as a search form, built-in fields have an implicit wildcard before/after included. But custom fields do not. This is confusing for the end user.

For example, if I put "ria" in the firstname field (a built-in field), it will return everyone with the firstname Brian.
If I put "new" in a custom field, it will return records that ONLY have "new" in that field -- it will not include records with "new york", "new jersey", "something new", etc. --- in other words, it doesn't run the search as %new%.

Before I add to the issue tracker, I wanted to see if others have noticed this issue, and if the core team has feedback. Also whether there's an easy fix (I've played around with the search pages, but haven't been able to hack a solution).

-Brian

Donald Lobo · December 28, 2007, 09:54:22 am

The main reason we stopped using wildcards (specifically %XX%) was to avoid sequential scans on the tables. If we do use wildcards, the performance gets pretty awful for medium-large datasets. We specifically turned on wildcards for standard fields to make the transition easier

. I suspect at some point we need to move the "wildcard" choice to the admin and let them make the tradeoff between performance and usability

(this is probably not before 2.2)

Having written the above, i looked at the code, and think there is a bug with the way we handle profile wildcards. Can you please file an issue and we'll fix for 2.0 (most likely)

lobo

lcdweb · December 28, 2007, 10:32:20 am

For frontend users, I think it's pretty important to have the ability to turn those on. I can teach admin users to explicitly user wildcards where needed, but Joe and Jane public should be able to search in a "normal" way through profiles -- i.e. how they are used to doing it on most search tools.

I will file an issue. In the meantime, is there any easy way to "turn on" the wildcards for custom fields? Even if it's less efficient. My dataset for this particular organization is not that large. But there are some key custom fields that they really need to be able to search on easily.

Thanks -
Brian

Donald Lobo · December 28, 2007, 10:38:19 am

File CRM/Contact/BAO/Query.php, line 919, change

Code: [Select]

        } else {
            $result = array( $id, '=', $values, 0, $wildcard );
        }

TO

Code: [Select]

        } else {
            $op = $wildcard ? 'LIKE' : '=';
            $result = array( $id, $op, $values, 0, $wildcard );
        }

not tested it, but i suspect it will fix things. please report back with the results. am off to sleep

lobo

lcdweb · December 28, 2007, 03:02:21 pm

That didn't work. It swaps "LIKE" for "=" in the WHERE statement, but it still lacks the wildcards.

I tried adding

Code: [Select]

$values = "%$values%"; as you had in other similar statements in that file, but it crashes the query. Any other ideas?

I created an issue in Jira: http://issues.civicrm.org/jira/browse/CRM-2529

Donald Lobo · December 28, 2007, 06:49:12 pm

I'll take a look and fix the issue over the weekend. Not sure why the query fails / blows up

lobo

lcdweb · December 28, 2007, 06:53:57 pm

For clarification --
With the edit you suggested, it simply doesn't work (i.e. it doesn't blow up, but it also doesn't give the expected result). I see in the query that it replaces LIKE for =, but it doesn't handle the wildcards. It only blows up with the additional edit I tried (inserting the wildcards into the values string as with other cases in that file).

Donald Lobo · December 29, 2007, 06:33:43 am

can u try with this patch instead (u can revert the previous patch)

http://fisheye.civicrm.org/changelog/CiviCRM/trunk?cs=12736

lobo

lcdweb · December 29, 2007, 09:03:04 am

That blew things up. Here's part of the error:

Code: [Select]

Database Error Code: Unknown column 'f.option_group_id' in 'field list', 1054
I'm guessing there are other changes made to that file for 2.0 that mean it's not compatible with the 1.9 install I'm running.

Donald Lobo · December 29, 2007, 09:50:34 am

there are quite a few changes to that file in 2.0. You should patch the 1.9 version. the important part of the patch to apply is to change line 314 (of CRM/Core/BAO/CustomGroup.php from

Code: [Select]

                           $val = CRM_Utils_Type::escape( strtolower(trim($value)), 'String' );

TO

Code: [Select]

                            $val = CRM_Utils_Type::escape( strtolower(trim($value)), 'String' );
                            if ( $wildcard ) {
                                $val = "%$val%";
                                $op  = 'LIKE';
                            }

lobo

lcdweb · December 29, 2007, 10:16:23 am

Excellent. That worked like a charm. Edit is actually to CustomQuery.php, as in the original patch suggestion.

Thanks!
Brian

CiviCRM Community Forums (archive)

News:

Author Topic: Profile Search Form -- Implicit wildcards missing from custom fields (Read 2385 times)

lcdweb

Profile Search Form -- Implicit wildcards missing from custom fields

Donald Lobo

Re: Profile Search Form -- Implicit wildcards missing from custom fields

lcdweb

Re: Profile Search Form -- Implicit wildcards missing from custom fields

Donald Lobo

Re: Profile Search Form -- Implicit wildcards missing from custom fields

lcdweb

Re: Profile Search Form -- Implicit wildcards missing from custom fields

Donald Lobo

Re: Profile Search Form -- Implicit wildcards missing from custom fields

lcdweb

Re: Profile Search Form -- Implicit wildcards missing from custom fields

Donald Lobo

Re: Profile Search Form -- Implicit wildcards missing from custom fields

lcdweb

Re: Profile Search Form -- Implicit wildcards missing from custom fields

Donald Lobo

Re: Profile Search Form -- Implicit wildcards missing from custom fields

lcdweb

Re: Profile Search Form -- Implicit wildcards missing from custom fields