How would you go about making a search algorithm for a CRM?

Question

I know this question is kind of broad, but all I really need is some best-practice code structure or a link to a good tutorial.

I am working on a CRM that runs on php and mysql. Currently, our search query looks something like this:

SELECT * FROM contacts WHERE name LIKE '%ric flair%'

Now, that's really dumbed down, but that's what it amounts to. In fact, we are using prepared statements, firing off several 30+ line queries, and joining lots of tables to verify ownership and all the rest.

But if the user types rick flair or ric m flair, the search query will not find him. Now, I could split the string into three search terms, %ric%, %m%, %flair%, and work from there, but then I'm going to get every guy named Ric (and Rick for that matter), and then Ric Flair could be anywhere among the search results.

I feel like I'm doing this like an amateur, and when I look up info about search algorithms, all I can find is people wanting to be Google.

Any pro advice on this would be appreciated.

To Clarify

This is for a global search function that looks through contacts, phones, emails, tasks, sales opportunities, conversations, and notes, looking for anything that has or is connected to your search string (in this case, Ric Flair).

The main goal here is to have a "smarter" search, such that if you type rick flair or ric m. flair, it will still have a good chance of returning the contact ric flair at the top, even though you might have people named Rick in you contact list. Maybe that's because the last name matched, and that's more important? Or am I overthinking this?

Answer 1

You may want to have a look at Lucene. It's written in Java but you can use it from PHP through either its Zend port or Solr, although I think Solr might not be a solution since you probably want something embedded within your CMS.

The idea is that you can build an index from all the searchable data and then search that index. It has the advantage that it can be much faster than database queries. You can also implement a scoring system, so that some records show up higher in the results list if they are more relevant to the user. One possible downside is that you have to update the index each time the data is updated.

If the Lucene approach doesn't fit your scenario, you could go with a mix of PHP and database code. A view or a stored procedure could act as a surrogate index, aggregating the searchable data. You'd need to split strings by whitespaces and this could make it rather slow, but will get you there. The PHP code will be responsible for building the WHERE clause (you can have a look at how Solr implements query strategies for suggestions as source of inspiration). Assuming you have Id, Name and Score as index fields and two contact records with Id=1, FirstName="Ric", LastName="Flair" and Id=2, FirstName="Ric", LastName="Flare" the index records would be looking something like this:

+--------------+-------+
| Id | Name    | Score |
+----+---------+-------+
| 1  | Ric     |   1   |
+----+---------+-------+
| 1  | Flair   |   1   |
+----+---------+-------+
| 2  | Ric     |   1   |
+----+---------+-------+
| 2  | Flare   |   1   |
+----+---------+-------+

An example could look like this (SQL Server):

DECLARE @index TABLE (
    Id INT NOT NULL,
    Name NVARCHAR(50) NOT NULL,
    Score INT NOT NULL
)

INSERT INTO @index (Id, Name, Score) VALUES (1, 'Ric', 1)
INSERT INTO @index (Id, Name, Score) VALUES (1, 'Flair', 1)
INSERT INTO @index (Id, Name, Score) VALUES (2, 'Ric', 1)
INSERT INTO @index (Id, Name, Score) VALUES (2, 'Flare', 1)

SELECT
    Id,
    SUM(Score) AS Score
FROM
    @index
WHERE
    Name = 'Ric'
    OR Name = 'Flair'
GROUP BY
    Id
ORDER BY
    Score DESC

Ric Flair would have a higher score since it matches on both values, thus popping first in the search results.

The index could also contain title and summary fields to be used as values to be displayed in the search results page. Or you could join the results with a view that preselects those values.

You can toy around with the conditions in the WHERE clause or with the Score field (you could give different scores to different type of records or properties) to get a more refined search experience.

Answer 2

I would look into Elasticsearch.

Elasticsearch is sort of a document database. It's a bit like MongoDB, but it's designed for very sophisticated text searches. I believe Stack Exchange itself uses Elasticsearch for the search box at the top of the page. It's also part of ELK (which stands for Elasticsearch, Logstash, and Kibana). It's built on top of Lucene, but it has a lot of extra functionality for search that you wouldn't want to have to build yourself.

I found this package for using Elasticsearch from Laravel. You'll have to integrate it into your infrastructure somehow, but in return you'll get very powerful search capabilities without a lot of extra work.

Solr is similar; it's also built on Lucene and offers powerful search capabilities. Either of these is a good choice that's much more powerful than SQL LIKE and easier to use than Lucene directly.

Answer 3

You can normalize data to search for Ric Flair as a person entity first and then display contacts of this person. This is a way of Big CRM and is suitable if Ric is important for you, a customer. You should expect a sophisticated search control for person entities via many data attributes.

Often you don't want to create a person each time, for example as an alternative contact for an order. In this case you can't do much but provide operators with clear pattern and/or instruction on how to fill the name field while capturing data.

Split of search pattern by whitespaces of course would produce more results, however my guess is that users would not like it since this is not a standard pattern.