How to make search match all keywords / more google like

[Michael McAndrew]

Hi there,

This post is both a request for a quick hack and some feedback for a longer term solution.  It's similar to (and a +1 for) Xavier's questions / observations on search (http://forum.civicrm.org/index.php/topic,5369.0.html) that he made last year.

It would be really helpful if the search behaved (a bit) like google, i.e. treated spaces as a keyword seperator and matched records that contained all the criteria.  I hope that this is as simple as changing an OR to and AND in Contact\BAO\Query.php but there are a lot of ORs in that document - can you point me to right one?

Here's a few justifications for why I think it should be like this...

One scenario is that I don't know if the organisation is recorded in the database as ...
* Department of Education and Skills
* Department for Education and Skills
* Department for Skills and Education
* The Department for Skills and Education
etc.

This might be because I can't remember its name, or maybe because I know its there, so presume someone must have recorded it slightly wrong.  My instinct is to type "Department Skill Education" (without quotes) and see those contacts with all three strings.  (at the moment, I get lots of results that aren't anywhere near my match (and I don't think it is very often that people find this behaviour useful).

A similar scenario is that I type in Support to search for an organisation and I want to refine it, so type Family Support (and want it to return fewer results).

Both these scenarios are pretty common for database users and having CiviCRM behave in the way that they are used to experiencing in other search engines (like google/amazon/youtube etc.) would be great for usability, no?

[xavier]

Hi,

To have it work, you need a full text search engine. There is one on mysql but it isn't compatible with the table used for what I've seen.

I've hacked it a bit so it works for my case (lots of org with common names), but to be honest, that's not that good either.

What I found is that solr seems to be the right option and they have now and option to easily plug it on a db, but never had the budget/time to try.

Hope it helps, I'd love to hear if you can find a solution

X+

[Donald Lobo]

integrating solr into civicrm would definitely be cool and nice. however, i'm not sure how much traction it will get since you need a java stack to run it. But it does seem the right long term solution.

I'm not sure what hacks you are looking for. ping us on IRC and we can help u out

lobo

[xavier]

 MySQL has support for full-text indexing and searching, but "Full-text indexes can be used only with MyISAM tables"

If you want a full text search without going solr, you might want to create an myisam table "contact_searchable" (id+keywords, with a full_text index on the keywords blob) and add a trigger on civicrm_contact so everytime it updates a contact, it add the display_name+... on the contact_searchable or create a cron to do it.

There is some code to export to solr on the svn trunk at least, might be an example ?

X+

[Michael McAndrew]

Hi again,

Thanks both for your input.  I chatted with Lobo on IRC and the % wildcard is sufficient for 90% of my cases.

Going back to my example, Department%Education%Skills matches

* Department of Education and Skills
* Department for Education and Skills
* The Department for Education and Skills

Unfortunately, it doesn't match

* Department for Skills and Education

Any ideas on changing the behaviour so that does happen?

You could add the following example to the contact search help pop up to help explain a little more about using wildcards (as it isn't that obvious to the lay person).

"Bangladesh%Association" will return

Bangladeshi Women's Association
Queen's Park Bangladeshi Association

but not

Association of Bangladesh Community.

[Michael McAndrew]

Hi search gurus,

I did a bit more looking in the code (thanks lobo for pointers on where to look) and I think I am right in saying that when you add something to the "Complete OR partial Contact Name" search field, the logic is something like the following:

First look to see if it starts and ends with ". If that is the case, search for the entire string.

Else, see if it has a comma, and if so, again, search for the entire string,

Else, split it up into seperate key words and return contacts that contain ANY of these keywords.

I'm gonna go out on a limb and say that last part of the logic would be better implemented as "return contacts that contain ALL of these keywords".  You can do this by changing the OR to an AND in the following code.

You can use % wildcards enforce "consecutiveness" of the search terms.  The only thing you can't do is search for contacts that contain any of the terms, but who wants to do that?! ;-)

Code: [Select]

Contact\BAO\Query.php at around line 2170

                    $sub[] = " ( civicrm_email.email $op $value ) ";
                }
            }
        }

        $sub = ' ( ' . implode( ' AND ', $sub ) . ' ) ';
        $this->_where[$grouping][] = $sub;


Code: [Select]

Contact\BAO\Query.php at around line 2170

                    $sub[] = " ( civicrm_email.email $op $value ) ";
                }
            }
        }

        $sub = ' ( ' . implode( ' OR ', $sub ) . ' ) ';
        $this->_where[$grouping][] = $sub;

I think this improves the search and would be interested to hear your views.

Michael

[xavier]

Code: [Select]

Else, see if it has a comma, and if so, again, search for the entire string,

I don't understand the logic of that rule. What's the use case ?

Code: [Select]

Else, split it up into seperate key words and return contacts that contain ANY of these keywords.

I'm gonna go out on a limb and say that last part of the logic would be better implemented as "return contacts that contain ALL of these keywords".  You can do this by changing the OR to an AND in the following code.


Definitely make more sense, going to patch a big db with plenty of orgs full of common words, see if makes it better.

I suppose it only applies to contact/search and advanced, not the autocompete ajax ones, isn't it ? Did you hack them as well ?

X+

[Michael McAndrew]

Hi there,

I think the logic of adding the comma is so you can search on an exact name - but I don't think it's a very inituative way of handling search (it's certainly the only time I've seen searching implemented like that).

Would be good to know what the chances are of having this change request implemented.  I think it is obviously an improvement for the majority of users/use cases but given that it is a change that affects already existing functionality, do we need to have a wider discussion?

PS.  Haven't had enough experience with the ajax to see that it is a problem.

[Michael McAndrew]

Have realised that the AND / OR switch isn't as simple as I thought.  I still think it's the right behaviour to aim for but because of the complexities of the LIKE clauses between 2100 and 2160, it is a bit more involved than a simple subsitution.

Just doing the above for example means that org records won't show as it they need to satisfy conditions on fields that don't appear for orgs.  Won't go into the details but will hopefully work out a solution and post back.

Michael

[Donald Lobo]

I don't understand the logic of that rule. What's the use case ?

this came from a user request very early on (in 1.1 or so). exact details are a bit murky, but they wanted this feature

lobo

[Michael McAndrew]

OK - what is the proceedure for deciding on changing it back again, then ?  I do think it would be better to be AND by default but don't want to introduce a change that will break behaviour for already existing users.    maybe having an "OR:" operater at the beginning of the string similar and looking for this like we do the "," is the best way to satisfy all customers...

Am happy to look at this and submit a patch when I get the time.

[xavier]

It certainly makes sense to switch the default to AND, unless the search can be made "smarter" and put the contacts that contains all the words above the ones that contains only one.


Not convinced about the "OR:" syntax, but that's so much of an edge case that it doesn't matter much I suppose.

X+

[Donald Lobo]

I dont think only switching the OR to AND is a great solution, since a few months down the road someone else will come by and want the reverse and have an equally valid use case

I think changing the behavior to use AND if there is  the string "AND" as part of the search string is probably a good compromise for 2.2 and we can tweak it in 2.3

if u'll agree, who will submit a patch with the issue?

lobo

[petednz]

and??? - this was reading so well, then i feel that i missed out on the last chapter 

[Michael McAndrew]

Hey there...

I'm going to submit a patch for this (fingers crossed) in the next few days.

Lobo - happy to be persuaded but my instinct is that looking for any instance of AND in the string might make people think this functionality is cleverer than it actually is, i.e they might start thinking they can search for things like "youth AND group club".

My latest thoughts on how to configure this are:

1) add a variable that can be defined in the admin section that allows admins to switch between either behaviour.
2) Add the ability to override the default behavior by adding "AND:" and "OR:" modifiers at the start of the search code.

Does that satisfy all use cases and not make things too complex?

Issue here: http://issues.civicrm.org/jira/browse/CRM-4194