CiviCRM Community Forums (archive)

The string search algorithm takes each "word" in the string and appends a wild-card (%) after the word. Then it does an OR search on these elements.

So, a search for "City of Burlington" becomes:

SELECT .... WHERE sort_name LIKE 'city%' OR sort_name LIKE 'of%' OR sort_name LIKE 'burlington%'

This works pretty well for searches on peoples names ("Smith, Jane") - perhaps less well for multi-word organization names. We will be revisiting these rules/algorithms in 2.1.

For now, if you want the search to return items which contain (but don't start with) your search string - you'll need to prepend the string with the wildcard (%). So, to find organizations which contain 'Burlington' - search for '%Burlington'.

The search algorithm will automatically lower-case your string and append the wildcard to all "words" - so this will result in:

SELECT ... WHERE sort_name LIKE '%burlington%'

"%city of burlington" will produce this SELECT statement:

SELECT .... WHERE sort_name LIKE '%city%' OR sort_name LIKE 'of%' OR sort_name LIKE 'burlington%'

This will return names which contain 'city' regardless of what the starting characters/words are. As well as names which start with 'burlington' OR start with 'of'.

while "%burlington" gives you:

SELECT .... WHERE sort_name LIKE  '%burlington%'

This will return names which contain 'burlington' regardless of what the starting character/words are.

So the difference results are related to which word(s) have the wildcard at the beginning (which you put in manually) as well as at the end (which are put in automatically). Hope that helps.

Howdy

Long time since I updated this post. Is a Civi integration with Solr in the works? Is there a way to pull this in through Drupal's integration with it?

I ask this because I'm getting back to this original issue, but now with 3.0. Still having a bunch of issues. Trying to understand what's going on.

I have an organization, let's say "AA Jeans". This org has no email associated with it. Then I have a bunch of households also as "AA Jeans" without emails. Then I have a bunch of employees who work there, all with email address like "x@aajeans.com".

So when I type "=AA Jeans" (no quotes) into the search box, I get all of these results. This is excellent.

Second example. I have an organization called "Region Sports". There is no email associated with it. I have a bunch of households also as "Region Sports". and of course, employees with "@regionsports.com". Now, when I type '=Region Sports' into the search, I get tons of results, anything with 'sports' in the name.

According to our discussion above, I'm assuming this is because I have other contacts in my db with the word 'sports' in their name, while I must not have any other contacts in my db with the word 'jeans' in their name. Am I correct?

Sadly, the whole double quotes thing is great except that it is too restrictive in my case. Meaning, because I might not have emails with my orgs and households, if I type "Region Sports" with the quotes into the search box, then it's only going to give me the organization. Where as if I type "regionsports" into the search box, it only gives me the individuals who work there (because of email addresses) and not the organization/households themselves.

This appears to be driving my staff crazy. Is there search/report I could build off to basically let you type in an org name and have it display search results for any and all relationships, including the main org? I was trying to do an advanced search and see if the 'belongs to' or 'includes' options under relationships might do this, but it also appears to restrict to one particular contact type (household OR individual, but not both). Also looking under reports, but I can tell my staff will find filling them out complicated and too much work. Trying to find some easy way to get this information in some kind of search.

A lot of information here. I'm looking for general input, suggestions, help, or pointers to where this is at now that we're at 3.0.

Thanks so much.
emilyf

thanks lobo; i figured you'd say that 

will keep a watch on solr as well as that will be quite the improvement in other areas. for anyone else interested, here's another good thread on searching: http://forum.civicrm.org/index.php/topic,6241.0.html